time calculation

[Bob]

I am creating a simple time sheet template which calculates hours and minutes worked in a week. I'm using a 24 hour clock.

I know how to create the basic template:EndTime minus StartTime with the result formatted as time.

What I need is help in rounding the minutes up/down to the nearest quarter hour so that 10 hrs 20 minutes of work is reported as 10 hours 15 minutes, but 10 hrs 25 minutes of work is reported as 10 hrs 30 minutes. The 'break point' is within 5 minutes of the quarter hour.

Any suggestions would be appreciated.

[Barry]

I am creating a simple time sheet template which calculates hours and minutes worked in a week. I'm using a 24 hour clock.

I know how to create the basic template:EndTime minus StartTime with the result formatted as time.

What I need is help in rounding the minutes up/down to the nearest quarter hour so that 10 hrs 20 minutes of work is reported as 10 hours 15 minutes, but 10 hrs 25 minutes of work is reported as 10 hrs 30 minutes. The 'break point' is within 5 minutes of the quarter hour.

Any suggestions would be appreciated.

Hi Bob,

Do you want the time for each job or the total for the week's jobs to be rounded? One job (work session)/day or multiple sessions? Are you working with the spreadsheet or with a database?

I'm assuming the former in each of the above cases.

Your 'break point description is ambiguous. Both 1:11 and 1:19 are "within 5 minutes of the quarter hour", and would, I assume, round to the same time. But neither 1:09 nor 1:21 is "within 5 minutes of [any] quarter hour". Which way would these two round?

"Time" in AppleWorks (and the Mac's clock) is always "Elapsed time", measured in Days and fractions of days starting at January 4, 1904. When you enter something AppleWorks recognizes as "time", the entry is converted into a number equivalent to the fraction of a 24 hour day that has elapsed up to that time, the resulting value is stored, and the cell's formatting is set to "Time", using the particular time format you entered. For example, if you enter:

"13:49", AppleWorks stores 0.57569444444, and displays 13:49
if you enter:
1:49 pm, AppleWorks stores 0.57569444444, and displays 1:49 PM

(Not quite an accurate description, as "0.57569444444" is simply the decimal (base ten) representation of the actual number stored.)

Adding and subtracting times works pretty seamlessly until you run into either of two situations:

1. The total exceeds a full day (ie. is more than 23 hours, 59 minutes and 59 seconds). If AppleWorks is asked to interpret a number greater than one (Day), it ignores the whole number part, and converts the decimal part to "Time of Day". Example: add 24 hours to the above times to get 37:47. The stored number is now 1.57569444444 (days). AppleWorks, in a cell formatted to display Time will show either 13:49 or 1:49 PM -- the same time of day as 24 hours earlier.

2. Subtractions involving times separated by a change of Date usually Display the correct result, but the stored result will be a negative number which, if included in a total, could give an unexpected result. This situation would arise anytime a job/session started before and ended after midnight, and is something I have NOT dealt with below).

Here's an example that deals with your question, and assumes that only the first problem above arises:

Start times listed in C3..C9 (one cell/day).
End times in D3..D9
Time worked (unrounded) in E3..E9
Rounded times, converted to hours and quarter hours (decimal representation), in F3..F9
Total for week in F10.

Formulas:

E3: =D3-C3, Format as Time, 13:59
F3: =ROUND(4*24*E3,0)/4
(Enter the above, then select cells E3..F9 and go Calculate> Fill Down)

F10: =SUM(F3..F9)

As written, the formula in F3 collects the time from E3, multiplies it by 24 to convert it from 'Days' to 'Hours' and by 4 to convert it to 'Quarter hours', rounds the result to the nearest whole number of 'Quarter Hours', and divides the result by 4 to convert back to 'Hours' and fractions of hours, expressed as a decimal.
Because the result is always a whole number (of quarter hours), the decimal part will always be .25, .5, or .75 (or .0, which will not be displayed). To ensure always displaying the fractional part, set the cell format for F3..F10 to Number, Fixed, 2.
The break point, using the formula as written, is at 7.5 minutes after the quarter hour--seven minutes will not be credited, eight minutes will count as a full quarter hour.

To change the break point to 5 minutes after the quarter use:

=ROUND(4*24*E3+1/6,0)/4

To change the break point to 5 minutes before the quarter use:

=ROUND(4*24*E3-1/6,0)/4

These formulas effectively add (or subtract) 2 minutes 30 seconds (1/6 of a Quarter Hour) to the raw time before rounding.

Regards,
Barry

[Guest]

I am creating a simple time sheet template which calculates hours and minutes worked in a week. I'm using a 24 hour clock.

I know how to create the basic template:EndTime minus StartTime with the result formatted as time.

What I need is help in rounding the minutes up/down to the nearest quarter hour so that 10 hrs 20 minutes of work is reported as 10 hours 15 minutes, but 10 hrs 25 minutes of work is reported as 10 hrs 30 minutes. The 'break point' is within 5 minutes of the quarter hour.

Any suggestions would be appreciated.

Hi Bob,

Do you want the time for each job or the total for the week's jobs to be rounded? One job (work session)/day or multiple sessions? Are you working with the spreadsheet or with a database?

I'm assuming the former in each of the above cases.

Your 'break point description is ambiguous. Both 1:11 and 1:19 are "within 5 minutes of the quarter hour", and would, I assume, round to the same time. But neither 1:09 nor 1:21 is "within 5 minutes of [any] quarter hour". Which way would these two round?

"Time" in AppleWorks (and the Mac's clock) is always "Elapsed time", measured in Days and fractions of days starting at January 4, 1904. When you enter something AppleWorks recognizes as "time", the entry is converted into a number equivalent to the fraction of a 24 hour day that has elapsed up to that time, the resulting value is stored, and the cell's formatting is set to "Time", using the particular time format you entered. For example, if you enter:

"13:49", AppleWorks stores 0.57569444444, and displays 13:49
if you enter:
1:49 pm, AppleWorks stores 0.57569444444, and displays 1:49 PM

(Not quite an accurate description, as "0.57569444444" is simply the decimal (base ten) representation of the actual number stored.)

Adding and subtracting times works pretty seamlessly until you run into either of two situations:

1. The total exceeds a full day (ie. is more than 23 hours, 59 minutes and 59 seconds). If AppleWorks is asked to interpret a number greater than one (Day), it ignores the whole number part, and converts the decimal part to "Time of Day". Example: add 24 hours to the above times to get 37:47. The stored number is now 1.57569444444 (days). AppleWorks, in a cell formatted to display Time will show either 13:49 or 1:49 PM -- the same time of day as 24 hours earlier.

2. Subtractions involving times separated by a change of Date usually Display the correct result, but the stored result will be a negative number which, if included in a total, could give an unexpected result. This situation would arise anytime a job/session started before and ended after midnight, and is something I have NOT dealt with below).

Here's an example that deals with your question, and assumes that only the first problem above arises:

Start times listed in C3..C9 (one cell/day).
End times in D3..D9
Time worked (unrounded) in E3..E9
Rounded times, converted to hours and quarter hours (decimal representation), in F3..F9
Total for week in F10.

Formulas:

E3: =D3-C3, Format as Time, 13:59
F3: =ROUND(4*24*E3,0)/4
(Enter the above, then select cells E3..F9 and go Calculate> Fill Down)

F10: =SUM(F3..F9)

As written, the formula in F3 collects the time from E3, multiplies it by 24 to convert it from 'Days' to 'Hours' and by 4 to convert it to 'Quarter hours', rounds the result to the nearest whole number of 'Quarter Hours', and divides the result by 4 to convert back to 'Hours' and fractions of hours, expressed as a decimal.
Because the result is always a whole number (of quarter hours), the decimal part will always be .25, .5, or .75 (or .0, which will not be displayed). To ensure always displaying the fractional part, set the cell format for F3..F10 to Number, Fixed, 2.
The break point, using the formula as written, is at 7.5 minutes after the quarter hour--seven minutes will not be credited, eight minutes will count as a full quarter hour.

To change the break point to 5 minutes after the quarter use:

=ROUND(4*24*E3+1/6,0)/4

To change the break point to 5 minutes before the quarter use:

=ROUND(4*24*E3-1/6,0)/4

These formulas effectively add (or subtract) 2 minutes 30 seconds (1/6 of a Quarter Hour) to the raw time before rounding.

Regards,
Barry

Haven't tried your formulae yet, but your description sounds like what I want. In our company, 7:09 is 7 o'clock & 7:10 is 7:15, but that's not written in stone.

Our night time shifts are scheduled to end at midnight; but most often it's sometime after midnight that our employees punch-out. A shift might go from 5 p.m. till 12:23 a.m., for example.

We generally collect hours-worked on a day-by-day basis in quarter hour intervals then add the total at the end of the work week.

Thanks for the suggestions. If you have any further insight, it would be appreciated.

Bob

[Barry]

Haven't tried your formulae yet, but your description sounds like what I want. In our company, 7:09 is 7 o'clock & 7:10 is 7:15, but that's not written in stone.

Our night time shifts are scheduled to end at midnight; but most often it's sometime after midnight that our employees punch-out. A shift might go from 5 p.m. till 12:23 a.m., for example.

We generally collect hours-worked on a day-by-day basis in quarter hour intervals then add the total at the end of the work week.

Thanks for the suggestions. If you have any further insight, it would be appreciated.

Bob

Hi Bob,

To add correct handling of shifts that crossover into a new date, try this version of the formula for cell E3:

E3: =(D3-C3)+(D3<C3)

Format as Time, 13:59.

Both this and the earlier version will display the correct "7:23" time in E3 using the times in your example above. But the original has an actual result of -0.69 (to two decimal places), which will wreak havoc on the week's total.

The part following the + sign evaluates as 1 if TRUE and 0 if FALSE. Assuming End time will be less than Start time only if the End time is in the next calendar day, the formula adds 1 (day) to the result to make it the correct positive number.

Regards
Barry

[Guest]

Bob[/quote]

Hi Bob,

To add correct handling of shifts that crossover into a new date, try this version of the formula for cell E3:

E3: =(D3-C3)+(D3<C3)

Format as Time, 13:59.

Both this and the earlier version will display the correct "7:23" time in E3 using the times in your example above. But the original has an actual result of -0.69 (to two decimal places), which will wreak havoc on the week's total.

The part following the + sign evaluates as 1 if TRUE and 0 if FALSE. Assuming End time will be less than Start time only if the End time is in the next calendar day, the formula adds 1 (day) to the result to make it the correct positive number.

Regards
Barry[/quote]

Barry, thanks for your help. If I run into a problem, I'll post here. Thanks again.