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.

 

[truebluewoman]

Try this:

=IF(E2="Absent",8,IF(E2="Arrived Late",F2-I2,IF(E2="Left Early",L2-G2,0)))

 

[paccali]

Try this:

=IF(E2="Absent",8,IF(E2="Arrived Late",F2-I2,IF(E2="Left Early",L2-G2,0)))

Thank you for your quick response! I just had a question on how Column H (Total Hours/Minutes Misses) should be formatted. Arrived Late and Left Early need to be calculated as hours and minutes (Ex. Arrived Late 2.00). Do you know how to fix this?

 

[truebluewoman]

You would right-click and select Format Cells

Go to Custom and then use h:mm.

 

[paccali]

This was the result, do you know what step I'm missing? Do I need to change the formula?

 

[truebluewoman]

Line 4 looks correct. It displays 25 minutes (5:00 - 4:35) I need to see the formula in line 3. That should show 2:00 for 2 hours. Line 1 should show 8:00. Can you confirm that there are no trailing spaces in the E column? If you have "Absent " vs. "Absent", it will not return anything.

 

[paccali]

Line 4 looks correct. It displays 25 minutes (5:00 - 4:35) I need to see the formula in line 3. That should show 2:00 for 2 hours. Line 1 should show 8:00. Can you confirm that there are no trailing spaces in the E column? If you have "Absent " vs. "Absent", it will not return anything.

I'm sorry you were right regarding "Arrived Late"(I had a space after Late), but Absent didn't change after I made the correction.

 

[paccali]

View attachment 32914
I'm sorry you were right regarding "Arrived Late"(I had a space after Late), but Absent didn't change after I made the correction.

Also 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]

Oops. Change the 8 to 1/3. The format uses a 24 hour day, so to show 8 hours, you'd have to enter 1/3.

=IF(E2="Absent",1/3,IF(E2="Arrived Late",F2-I2,IF(E2="Left Early",L2-G2,0)))

 

[paccali]

Oops. Change the 8 to 1/3. The format uses a 24 hour day, so to show 8 hours, you'd have to enter 1/3.

=IF(E2="Absent",1/3,IF(E2="Arrived Late",F2-I2,IF(E2="Left Early",L2-G2,0)))

That fixed it Thank you