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


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!



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



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.



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