TIMESHEET - docking hours if > or < than 5 mins

[dieseldycke]

Can somebody help me, our company has this 5 mins grace period before and after the quarter of an hour, and either rounded up or down, example:

A1 In - 06:55 (docked at 07:00)
B1 Out - 15:36 (docked at 15:45)
C1 Total of 8.75 Hours

another example:

A1 In - 15:21 (docked at 15:30)
B1 Out - 20:54 ( docked at 20:45)
C1 Total of 5.5 Hours

I know there is a way to get to this total hours, i just cant get the right formula. Thanks in advance

 

[joeu2004]

The rule is unclear to me. Please clarify -- and perhaps provide some more examples within and outside the 5m grace period.

Explain like this:

A1 In - 06:55 (docked at 07:00) round up because 6:55 is within 5min before 7:00?
B1 Out - 15:36 (docked at 15:45) round up because 15:36 is more than 5min after 15:30?
C1 Total of 8.75 Hours because 15:45 - 7:00 is 8h 45m

A2 In - 15:21 (docked at 15:30) round up because 15:21 is more than 5min after 15:15?
B2 Out - 20:54 ( docked at 20:45) huh?! I would think: round up to 21:00 because 20:54 is more than 5min after 20:45?
C2 Total of 5.5 Hours huh?! 20:45 - 15:30 is 5h 15m. So should the total be 5.25h? OTHO, 21:00 - 15:30 would indeed be 5h 30m (5.5h)

These are all examples of rounding up. (Unless you explain why B2 is rounded down.)

Please provide some examples of rounding down. (I presume they would be with 5min of the lower quarter-hour.)

Does the formula also need to work for shifts that start one day and end within 24h the next day?

If so, do your timestamps include the date (even if they are formatted to show only the time)?

 

[dieseldycke]

ooops on C2, should have been 5.25 hours, and take note on the final hours its in decimals

 

[dieseldycke]

The rule is unclear to me. Please clarify -- and perhaps provide some more examples within and outside the 5m grace period.

Explain like this:

A1 In - 06:55 (docked at 07:00) round up because 6:55 is within 5min before 7:00?
B1 Out - 15:36 (docked at 15:45) round up because 15:36 is more than 5min after 15:30?
C1 Total of 8.75 Hours because 15:45 - 7:00 is 8h 45m

A2 In - 15:21 (docked at 15:30) round up because 15:21 is more than 5min after 15:15?
B2 Out - 20:54 ( docked at 20:45) huh?! I would think: round up to 21:00 because 20:54 is more than 5min after 20:45?
C2 Total of 5.5 Hours huh?! 20:45 - 15:30 is 5h 15m. So should the total be 5.25h? OTHO, 21:00 - 15:30 would indeed be 5h 30m (5.5h)

These are all examples of rounding up. (Unless you explain why B2 is rounded down.)

Please provide some examples of rounding down. (I presume they would be with 5min of the lower quarter-hour.)

Does the formula also need to work for shifts that start one day and end within 24h the next day?

If so, do your timestamps include the date (even if they are formatted to show only the time)?

thanks for taking the time, i am testing this formula

B1=TIME(HOUR(A1),INT((MINUTE(A1)+9))/15)*15,0)

but have not tested it yet

 

[joeu2004]

=TIME(HOUR(A1),INT((MINUTE(A1)+9))/15)*15,0)

You have a misplaced parenthesis, highlighted in red. Simply delete it.

Then that formula does what __I__ expect. In particular, 20:54 is indeed rounded up to 21:00; and the elapsed time from 15:21 (15:30) to 20:54 (21:00) is indeed 5.5.

The rule seems to be: if the time is 5m or less after a quarter-hour, round down to that quarter-hour; otherwise, round up to the next quarter-hour.

Note that 23:51 is rounded "up" to 0:00 (midnight), not 24:00. That seems right to me.

 

[dieseldycke]

You have a misplaced parenthesis, highlighted in red. Simply delete it.

Then that formula does what __I__ expect. In particular, 20:54 is indeed rounded up to 21:00; and the elapsed time from 15:21 (15:30) to 20:54 (21:00) is indeed 5.5.

The rule seems to be: if the time is 5m or less after a quarter-hour, round down to that quarter-hour; otherwise, round up to the next quarter-hour.

Note that 23:51 is rounded "up" to 0:00 (midnight), not 24:00. That seems right to me.

thank you chief, it is a working formula after all.... although i want ask how can i make my sheet user-friendly, by entering just the four digits representing the time, without having to use the colon e.g. 0135 is 1:35 am....

 

[joeu2004]

how can i make my sheet user-friendly, by entering just the four digits representing the time, without having to use the colon e.g. 0135 is 1:35 am

Yes. And you can use the Custom format 00\:00 or 0\:00 so that it is displayed as 01:35 or 1:35 instead of 0135.

However, the actual value is still 135 (one hundred thirty-five), which is not usable for your calculations.

I would enter formulas of the following form in a parallel column to convert to Excel time:

=--TEXT(A1,"00\:00")

formatted as Time in some form (hh:mm or h:mm AM/PM).

Alternatively, you must use a conversion in your round-up/down formula, to wit:

=TIME(INT(A1/100), FLOOR(MOD(A1,100)+9,15), 0)

formatted as Custom [h]:mm .

(I always use [h] or [hh] instead of h or hh, if only to be sure that h does not exceed 23 unintentionally, and to see h > 23 when intended.)

BTW, the FLOOR function can be used to simplify the original formula (when A1 is 01:35, not 0135), to wit:

=TIME(HOUR(A1), FLOOR(MINUTE(A1)+9,15), 0)

Gotta run! I'll review and correct mistakes later.

 

[dieseldycke]

Yes. And you can use the Custom format 00\:00 or 0\:00 so that it is displayed as 01:35 or 1:35 instead of 0135.

However, the actual value is still 135 (one hundred thirty-five), which is not usable for your calculations.

I would enter formulas of the following form in a parallel column to convert to Excel time:

=--TEXT(A1,"00\:00")

formatted as Time in some form (hh:mm or h:mm AM/PM).

Alternatively, you must use a conversion in your round-up/down formula, to wit:

=TIME(INT(A1/100), FLOOR(MOD(A1,100)+9,15), 0)

formatted as Custom [h]:mm .

(I always use [h] or [hh] instead of h or hh, if only to be sure that h does not exceed 23 unintentionally, and to see h > 23 when intended.)

BTW, the FLOOR function can be used to simplify the original formula (when A1 is 01:35, not 0135), to wit:

=TIME(HOUR(A1), FLOOR(MINUTE(A1)+9,15), 0)

Gotta run! I'll review and correct mistakes later.

what it is right now is perfect, i just happened to be a slow typist and wishing on shortcuts because i will be entering lots of numbers in that sheet, but i will try your formula and formatting, thank you so much youre very helpful.