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 & 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

[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

"Sasha" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )


"Sasha" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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).

Formulas that I've been using so far are:

=SUM(B1-A1+(A1>B1))*24-IF((B1>A1),B1-A1,B1-0)*24 for basic time, and:

=IF((B1>A1),B1-A1,B1-0)*24 for overtime.

Formulas work fine, but if someone is working let's say from 17:00 till 23:00 it calculates it as overtime.

Any ideas how to fix this?

Thanks,
Sasha

[Biff]

"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

[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