Time clock formula help requested

[115shelly]

Good Evening ( NY time),

I have a H:mm:ss issue. We pulled time clock data into excel. The intent is to calculate the amount of hrs / day and week and compare that to a 40 hr week.
then compare the time punched in to the start time and calculate the amount of time ( if any ) is punched prior to the allowed start time. ( the clock has a 15 minute rounding rule).

It seemed like a very straight forward simple set of calculations, somehow i cant get it right. I have adjusted the formatting of time cells, to no avail.

Link: Attached is an example sheet with "sample data" and a explanation of what I am trying to achieve in each column.

Dropbox

www.dropbox.com

Thank you in advance for you assistance. I appreciate your help.

Jason

 

[cyrilbrd]

G11 format as [h]:mm:ss;@

 

[115shelly]

cyrilbrd

Thanks for your help on G11 !

Any chance you could help on the formula in "H"?
Best - jason

 

[cyrilbrd]

Maybe H2 copied down =IF(ISBLANK(E20),"",IF(E20-D20<=0.0104166666666667,0,E20-D20-0.0104166666666667)) format is h:mm:ss