Math with Time

[nikneven]

ok, so in my spreed sheet i need cell to do some time calculations. Basically it is payroll for employees. They get paid overtime for everything between midnight and 6am. its set up like this...

A1 is the Start Time
A2 is the Duration
T1 needs to be number of hours worked between 6am and 11:59 pm
T2 needs to be number of hours worked between 12:00am and 5:59 pm

so, if

A1 =4 PM
A2= 10
then i need a formula to make T1 = 8 and T2 = 2

and the kick, it needs to span days, so if
A1=10pm
A2=10
T1=4
T2=6.
Get it?
help! Excels date and time stuff boggles my mind...

thanks
nik

 

[barry houdini]

Hi nik,

assuming A1 contains a valid time, e.g. 10 pm or 22:00 etc. and A2 is 18 or less then try this formula in T2

=IF(A1+A2/24<1,MAX(0,MIN(6,A1*24+A2)-A1*24),MIN(6,MOD(A1*24+A2,24)))

and this in T1

=A2-T2

 

[nikneven]

will that not work for anything over 18? sometimes it goes up to twenty.

 

[barry houdini]

I was trying to avoid overcomplicating the formula but to accommodate up to 20 hours change formula in T2 to this

=IF(A1+A2/24<1,MAX(0,MIN(6,A1*24+A2)-A1*24),MIN(6,MOD(A1*24+A2,24))+MAX(0,6-A1*24))