Time difference calculation

[Sasha]

Hi,

I am trying to make time sheet that would forecast employment cost.
Payment criteria is that overtime applies after midnight.
Sheet that I've designed has four columns per day IE. start & finis
time for AM shift IE. (08:00 - 16:00) and start and finish for P
shift. That could be (17:00 - 23:00), which doesn't create problem s
far but when inputting something like (18:00 - 04:00) i get total hour
worked. What sort of formula should I use that would separate basi
hours from overtime hours automatically IE. (6hrs basic and 4hr
overtime).

Any help on this matter would be greatly appreciated :

 

[Biff]

Hi!

A1 = start time = 18:00
B1 = end time = 4:00

For Reg hrs:

=IF(COUNT(A1:B1)<2,"",IF(B1<A1,(1-A1)*24,(B1-A1)*24))

For OT hrs:

=IF(COUNT(A1:B1)<2,"",IF(B1<A1,B1*24,0))

Biff

 

[Arvi Laanemets]

Hi

The general formula for time difference calculating with midnight rollovet
taken into account
=B1-A1+(B1<A1)
where A1 contains start time, and B1 contains end time.

When allways basic time is up to 6 hours, and all over 6 hours is overtime,
then
Basic time
=MIN(6/24,B1-A1+(B1<A1))
Overtime
=MAX(0,B1-A1+(B1<A1)-6/24)

 

[Sasha]

Thanks Biff,

Formulas work very well, thanks
the only time problem occurs is when shift starts at midnight
IE (00:00 - 05:00) formula calculates all the time as basic when it
should be overtime (any hours done after midnight).

Any ideas?

Thanks,
Sasha

 

[Biff]

Thanks Biff,
Formulas work very well, thanks
the only time problem occurs is when shift starts at midnight
IE (00:00 - 05:00) formula calculates all the time as basic when it
should be overtime (any hours done after midnight).

Well, you didn't mention that shifts can start after midnight in your
original post!

If Day shift starts at 8:00 AM does that mean any shift start from 12:00 AM
to 7:59 AM is overtime?

Biff

 

[Sasha]

Hi guys,

Thanks to Arvi problem is solved now

Formula for basic time is:
=IF(B1<A1,1,MAX(8/24,B1))*24-MAX(8/24,A1)*24

And formula for overtime is:
=((B1<A1)*B1 + AND(B1>A1,A1<8/24)*(MIN(8/24,B1)-A1))*24

Workes well so far !

Thanks to all who tried to help

Regards,
Sasha