Need help on formula for lates in excel

[stealth_0206]

Please help me on these two problems I have:

1) I just got this on some other sites

=IF(((E4-C4)*1440)<5,"On Time","Late")

the above shows that a grace period of 5 minutes is given so if for example an employee clocked in at 6:05 am therefore he is on time. My problem here is that, the word "on time" always show up even though there are no inputs yet in the time-in field. Can anyone help me to just have it blank if no inputs yet is given?


2) the above formula is just for the remarks if the employee is late or on time. Now what I want is another formula which will show the actual minutes late if the employee is late. This could have been simple if no grace period to consider. But what I want is that, if an employee clocks in at 6:05am then the late column will show "0" since a grace period is in effect. However, if an employee clocks in at 6:06am, then the late column will show "6" as the no. of minutes late. Is this possible?

I would really appreciate your help on this matter. Thank you very much!

[npamcpp]

you could check if one or both cells contain numbers. For example to check if both cells contain numbers, use

=IF(COUNT(E4,C4)=2,IF(((E4-C4)*1440)<5,"On Time","Late"),"")

If this formula is in F4, then you can show how many minutes late in G4 with this formula

=IF(F4="late",E4-C4,"")

Format G4 with custom format [mm]

[stealth_0206]

Thank you sir! Exactly just what I've wanted... I really appreciate it! God bless! ^_^