Time difference calculation
 

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 & finish
time for AM shift IE. (08:00 - 16:00) and start and finish for PM
shift. That could be (17:00 - 23:00), which doesn't create problem so
far but when inputting something like (18:00 - 04:00) i get total hours
worked. What sort of formula should I use that would separate basic
hours from overtime hours automatically IE. (6hrs basic and 4hrs
overtime).

Any help on this matter would be greatly appreciated :)


--
Sasha
------------------------------------------------------------------------
Sasha's Profile: http://www.excelforum.com/member.php...o&userid=36608
View this thread: http://www.excelforum.com/showthread...hreadid=563527

Time difference calculation
 

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

"Sasha" wrote in
message ...

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 & finish
time for AM shift IE. (08:00 - 16:00) and start and finish for PM
shift. That could be (17:00 - 23:00), which doesn't create problem so
far but when inputting something like (18:00 - 04:00) i get total hours
worked. What sort of formula should I use that would separate basic
hours from overtime hours automatically IE. (6hrs basic and 4hrs
overtime).

Any help on this matter would be greatly appreciated :)


--
Sasha
------------------------------------------------------------------------
Sasha's Profile:
http://www.excelforum.com/member.php...o&userid=36608
View this thread: http://www.excelforum.com/showthread...hreadid=563527

Time difference calculation
 

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)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Sasha" wrote in
message ...

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 & finish
time for AM shift IE. (08:00 - 16:00) and start and finish for PM
shift. That could be (17:00 - 23:00), which doesn't create problem so
far but when inputting something like (18:00 - 04:00) i get total hours
worked. What sort of formula should I use that would separate basic
hours from overtime hours automatically IE. (6hrs basic and 4hrs
overtime).

Any help on this matter would be greatly appreciated :)


--
Sasha
------------------------------------------------------------------------
Sasha's Profile:
http://www.excelforum.com/member.php...o&userid=36608
View this thread: http://www.excelforum.com/showthread...hreadid=563527

Time difference calculation
 

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


--
Sasha
------------------------------------------------------------------------
Sasha's Profile: http://www.excelforum.com/member.php...o&userid=36608
View this thread: http://www.excelforum.com/showthread...hreadid=563527

Time difference calculation
 

"Sasha" wrote....
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

Time difference calculation
 

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(B1A1,A1<8/24)*(MIN(8/24,B1)-A1))*24

Workes well so far !

Thanks to all who tried to help :-)

Regards,
Sasha


--
Sasha
------------------------------------------------------------------------
Sasha's Profile: http://www.excelforum.com/member.php...o&userid=36608
View this thread: http://www.excelforum.com/showthread...hreadid=563527