IF FUNCTION for time

For example, an employee is given 1 hour lunch break everyday. On a particular day, he has a time in of 12:15pm and time out of 1:18pm for lunch hour. He was late for 2 minutes.

If I input the time in and time out in each cell, what formula do I have to use for excel to automatically regard and input it on another cell as late for 2 minutes? And if he's not late, no action should be done. I figured using the IF FUNCTION would be my best bet, but I have trouble figuring out how to construct the formula.

Thank you for your help! If you need any more info, just ask me

Originally Posted by FDibbins

Assuming your data is in A1 (12:15) and B1 (1:18), use this...
=IF(B1-A1>1/24,B1-A1,"")
This will calce to ONE hour
Keep in mind that as far as excel is concerned, it treats time as a fraction/decimal of 1 (day). So 6:00am is actually 0.25, 12:00 mid-day is 0.5 etc

Thank you for your reply! But what if the time is in Sheet 1 and I need to input the late time in Sheet 2?

And do I change the 1/24 to 1/1440 if I want it in minutes?