Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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