Formula help

[paccali]

Hello,
I need help formulating. I'm working on an employee attendance sheet. I need to formulate the total hours/minutes missed. If they were Absent it would equal 8 hours; if they Arrived Late the total amount of hours/minutes they missed; if they Left Early the total amount of hours/minutes missed. I just can't figure it out and need some helpful insight.

 

[paccali]

One last question...I don't know how get around the issue if they Arrived Late after 1pm how to automatically subtract the 1 hour break so it doesn't equal 5 hours.

 

[truebluewoman]

I haven't given up on you. Still trying to figure out a rule that works for all .

 

[truebluewoman]

One last question: What would you want to see if someone arrived at 12:45 PM?

 

[paccali]

One last question: What would you want to see if someone arrived at 12:45 PM?

Lol I know it gets tricky. If they arrive at 12:45pm they would have skipped the lunch break (basically they would have missed 4 hours, but worked 5.15 hours...but I’ve already made this formula difficult enough; let’s just assume they won’t arrive during the daily break. Thank you again I really appreciate the help.

 

[truebluewoman]

Okay...This formula will work with the assumption that no one will arrive during the break hour. It uses two conditions for each scenario. Arriving Early before a break and after a break. Leaving Early before and after the break time. Everything else returns 0. Good luck!

=IF(E2="Absent",1/3,IF(AND(E2="Arrived Late",F2<=J2),F2-I2,IF(AND(E2="Arrived Late",F2>=J2),J2-I2+F2-K2,IF(AND(E2="Left Early",G2>=K2),L2-G2,IF(AND(E2="Left Early",G2<=J2),L2-K2+J2-G2,0)))))

 

[paccali]

Okay...This formula will work with the assumption that no one will arrive during the break hour. It uses two conditions for each scenario. Arriving Early before a break and after a break. Leaving Early before and after the break time. Everything else returns 0. Good luck!

=IF(E2="Absent",1/3,IF(AND(E2="Arrived Late",F2<=J2),F2-I2,IF(AND(E2="Arrived Late",F2>=J2),J2-I2+F2-K2,IF(AND(E2="Left Early",G2>=K2),L2-G2,IF(AND(E2="Left Early",G2<=J2),L2-K2+J2-G2,0)))))

Thank you so much for your help!