calculating overtime hours in excel

Question

I'm trying to make spreadsheet that calculate overtime hours as the following: If the task started after 2pm the hour is calculated as an hour and a half if the task started after 12 am the hour is calculated as 2 hours I wrote the following formula in column E:

 =IF(AND(B5>G1;B5<G2);D5*D1;D5*D2)

but I get false results where did I went wrong?

thank you

Answer 1

The problem with your formula is that you are thinking of 12:00 AM as midnight. or 24:00. If you look at G2 you will see 12:00 AM or (24:00) in Excel it is stored as 0. You logical check is checking to if anything is less than zero. Assuming that you are never dealing with negative time your result will always be false as no time is less than zero.

I would change your layout a bit and add a shift 3 where there is no overtime. What happens when someone starts at 9 AM?

IF you have to keep your layout as is, I would change your formula in cell D5 to read:

=HOUR(C5-B5)

And then make sure cells D1,D2,D5 and E5 are all general format since they are just numbers and not actual time.

Next set G2 equal to 08:00

change your formula in E5 to:

=IF(B5<G2,D5*D2,IF(B5>=G1,D5*D1,D5))
or
=D5*IF(B5<G2,D2,IF(B5>=G1,D1,1))

See if that gets you any further.

One thing to watch out for is that Excel stores time in a decimal format which can really throw you off. You may think your are dealing with 12 when you see 12:00 PM in a cell, but what excel really has stored in that cell is 0.5 as it is half way through the day. 24:00 does not exist Excel formulas will treat it as 0 if it is supplied as an input for formulas. VBA will not accept it. Valid Excel times range from 00:00 to 23:59.

now having said this if you are looking strictly at 8 hour shifts and are only concerned about start times, you could simply do an if statement saying IF(start time = shift 1 start time, then multiply by shift 1 rate, IF( start time = shift 2 start time, then multiply by shift 2 rate, no special rate))

something else to consider, if you are looking at actual start times, and a person starts in one shift but finishes during the next shift what happens?