Military Time to Calculate Hours worked after 7 pm

[PhilKubala]

Hi All,

This is my first post so I hope I can make my problem clear.

I am using Military Time format 00\:00 to calculate actual hours worked.

MONDAY
14-May-18
Start Finish Hours worked Calculate Hours worked past 7pm "19:00"
less 1 hr break

a 11:24 20:00 7.6 1
b 08:00 16:36 7.6 -2.4
c 12:24 21:00 7.6 2

The formula for calculating hours worked past 7pm is =IF(TEXT(G9*24>19,"00\:00"),(TEXT(G9,"00\:00")*24-19),(TEXT(0,"00\:00")))
Times for a: and c: are correct, however b: should be 0 as the finish time is 4:36pm "16:36" and not -2.4
How to I get the formula to read if False as b is to return a 0 value?

[Roel Jongman]

All you are doing with the 00/:00 is to present the number 1636 with a : between 16and 36 as if it was a thousand separator, when you do 1636*24 the outcome is 39264 so the outcome is always over 19 because even time 1minute past midnight will pass as 1*24=24>19 Since you do not have real time values the *24 does not work. that only works on true time (fraction of 1)

so you need to change your test and formula to

Please Login or Register  to view this content.

[Phuocam]

Try:

=IF(G8>1900,TEXT(G8,"00\:00")*24-19,0)

[Roel Jongman]

So I had another look at your formula, I now realize I took a different approach to determine under or over 7pm. but it is possible to do it the way you set it up, the formula just needs to be shifted around a bit..

this one will work too

Please Login or Register  to view this content.

so basicly there are more ways to get to where you want to be.
as a third option al that reformatting in the formula is not needed. militairy time is pronounced and written as 19 hundred hours. so dividing your outcomes by 100 will give also the right result

Please Login or Register  to view this content.

since you are out the get the hours worked as a number the last one might be the most logical

[PhilKubala]

Thank you , that was very helpful Roel.