Count formula using TIME after a specified TIME



I have created an hours time table calculating the shifts patterns of our workers and it looks similar to this:

    Start time Finish time  Deduct Lunch Total hours   Day off (Y/N) Week 1 Monday       0:30   Tuesday        0:30   Wednesday       0:30   Thursday        0:30   Friday        0:30   Saturday        0:30   Sunday       0:30           Total hours 0.00

I am looking to complete a total number of hours worked after 6pm and then another cell with total number of hours worked prior to 6pm. 


Start time: 09:00

Finish time: 21:00

Total hours: 11.5

Hours pre 6pm: 8.5

Hour after 6pm: 3

How do i create to cells that automatically calculate hours pre and post 6pm (i.e in the above example calculate 8.5 and 3) ? 

Many thanks. 




The time worked after 6pm is always the end time minus 6pm. For example, if the end time is in cell F5 the formula for calculating the overtime would be


0.75 is the equivalent of 6 pm. It is calculated as 1/24*18 (1 day divided into 24 hours, of which 18 hours have passed at 6 pm). With this formula you can calculate any time of day down to the 100th of a second (if you wish :-)


Answer the Question

You must create an account to use the forum. Create an Account or Login