Count formula using TIME after a specified TIME

0

Hi, 

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. 

Example: 

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. 



Answer
Discuss

Answers

0

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

=F5-0.75

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 :-)

Discuss

Answer the Question

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