Correct Calculation of a Time Sheet - Multiple Conditions

[Frejo]

Hi all,

I'm trying to sort a time sheet to calculate the true time I have spent on my assignment, in accordance with the rules the company has set.
The company sees the hours as 60/10, so in 6 minute intervals where the calculation is negative of course. I.e 15:00 is 15:00 until it is equal to or more than 15:06. There is also a lunch break of 42 minutes per day.
In my example I have managed to resolve all but 6 minute factor and also how to resolve double input, I.e splitting the work day in 2 shifts with the time in between minus the 42 minutes.

Any help on how to resolve this would be greatly appreciated.

Regards

Fred

[dflak]

I came up with a couple of formulas: (Note: the Lunch Break got pushed out to cell I2 because I added columns.)

=MROUND((C2-B2)-$I$2,6/1440) - this rounds the result to the nearest 6 minutes. (There are 1440 minutes in a day).

=HOUR(D2)/24 + 6*INT(MINUTE(D2)/6)/1440 - this rounds down to the end of the last 6 minute increment.

=HOUR((C2-B2)-$I$2)/24 + 6*INT(MINUTE((C2-B2)-$I$2)/6)/1440 - this is the same formula as above, but with the formulas for D2 substituted in where D2 appears.

As for the two shifts this might be accomplished by a pivot table. Let me "play" with that idea for a while

[dflak]

Well, that turned out to be easier than I thought it would be.

Use whatever formula is appropriate to compute the actual time, but leave out subtracting the break.

I converted the data range to an Excel Table - Excel Tables have a couple of advantages. One of them is that formulas copy down automatically. So when you enter a date in Cell A6, row A6 becomes part of the table and the formula in column E is copied to Cell E6 automatically. Also tables know how many rows they have, so pivot tables, formulas, charts, etc. that are built from them change when the table changes.

The pivot table was made with the date in the rows column and the Actual time and a calculates fieldas the values (I showed actual time only as a check for the calculation). I relabeled the column headers. Thealculated field in the pivot table to calculate the Time w/Break is ='Actual Time'-42/1440.

[Frejo]

It works like a charm! Thanks for the great tips and explanations.