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

Issues with calculating total time for 2nd shift, due to the excel assumes AM is less than PM.

0

I was tasked to calculate the total hour worked for each employee without meal and breaks time. However, I run into issues with calculating total time for 2nd shift. The excel  assumes AM is less than PM. Therefore, I am not able to calculate 2nd shift for people who timed out at 1:45 AM next morning. If anyone knows how to do it, please let me know! I have been thinking about it for a couple day.

Also, I am trying to put exact time into formula, but it doesn't work for me. For example, instead of 

= IF(AND(D3<$E$14,E3>$F$14,D3<$E$16,E3>$F$16),F3-1/3,IF(OR(AND(D3<$E$14,E3>$F$14),AND(D3<$E$16,E3>$F$16)),F3-1/6,F3))

I would like to use:

=IF(AND(D3< 9:00AM,E3>9:10AM,D3<1:30PM,E3>1:40PM),F3-1/3,IF(OR(AND(D3< 9:00AM,E3>9:10AM),AND(D3<1:30PM,E3>1:40PM)),F3-1/6,F3))

If anyone knows how to make them work, please let me know!

exceldata-analysisdate-timecommentssort-filter
posted by: Tris on Wed Aug 16, 2017 at 11:18 am
Answer
Discuss

Discussion

Take a look at what  I wrote a few minutes ago in answer to a question rather similar to yours.
Variatus (rep: 4889) Aug 17, '17 at 7:54 am
Add to Discussion

Answers

0

Hello Tris

Total time of shifts in hours  = ((end time - start time) + (end time < start time))*24

Times are decimal portions of a 24 hour day. The (end time < start time) part gets evaluated to be True or False (1 or zero) so if the end time is less (the time crossed midnight) a 1 is added otherwise nothing is added.

In your formula, to use actual times they need to be in quotes
ie: = IF(AND(D3<"9:00 AM", etc.

posted by: nos on Wed Aug 16, 2017 at 11:20 pm
Discuss

Discussion

Hi, I input it into my attached excel, but the hours still not come out correct. Not sure why.
=IF(AND(H2< "9:00 AM",I2> "9:10 AM",H2< "1:30 PM",I2> "1:40 PM"),K2-1/3,IF(OR(AND(H2< "9:00 AM",I2> "9:10:00 AM"),AND(H2< "1:30 PM",I2> "1:40 PM")),K2-1/6,K2))
I used time start at 6:00 AM and ends at 10:00 AM, but the formula doesn't excluded the break time.
Tris Aug 17, '17 at 10:50 am
Add to Discussion


Answer the Question

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