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!