Calculating a Premium Wage for a Specific Time Range

[Ranjodh]

I am trying to set up a spread sheet to calculate a premium wage (extra $0.25) for employees who work Midnight to 6am.

I have to keep it simple on the inputting side of things and would like the format to be:

Start Time | Finish Time | Regular Hours | Premium Hours

This can be used to pull the time range data:

00:00:00 | 06:00:00

What I need is a function that will sum both regular and premium hours, and separate them in different cell's.

I have tried a couple IF statements, and just can't get it to work right. Any help would be appreciated.

Thanks

[brokenbiscuits]

For regular hours (in C2):
=IF(B2<A2,B2+1-A2,B2-A2)-D2

and for Premium hours in D2:
=IF(AND(B2>A2,A2>0.25,B2>0.25),0,IF(A2<0.25,IF(B2>0.25,0.25-A2,B2-A2),IF(B2>0.25,0.25,B2)))

Should work?

[Ranjodh]

That is Perfect!!!! Thank you

[Ranjodh]

Sorry, It works perfect until I have 2 AM times, then it returns the formula as 0

[daddylonglegs]

Try this formula for premium hours

=(B2<A2)/4+MIN(1/4,MOD(B2,1))-MIN(1/4,MOD(A2,1))