Cross-referencing schedules

[NewbieToNerding]

Hi!

I have two schedules - a posted schedule, and one pulled from our time keeping system. I would love to set up a formula that will post the following:

If the times listed on both schedules match, or are within 6 minutes of each other, the cell will read "on time"
If the times listed on the Posted Schedule is 7 minutes or more before the time on the Actual Schedule, the cell will read "late"
If the time listed on the Posted Schedule is 7 minutes or more after the time on the Actual Schedule, the cell will read "early"


So far I've been able to piece this together:

=IFERROR(IF(C7>S7+TIME(0,6,0),"Early",IF(C7<S7+TIME(0,6,0),"Late",IF(OR(C7=S7+TIME(0,6,0),C7=S7-TIME(0,6,0)),"on time"))),"check sch'l")


Thank you!

 

[No_Need_For_Macros]

NtN,

I only used 6 minutes in the below formula. I.e. If the actual schedule is within 6 minutes (early or late) it's "On Time". If it is greater than 6 minutes on either end it's either "early" or "late". Column A contains Posted Schedule, Column B is Actual.

=IF(ABS(A2-B2)<=TIME(0,6,0),"On Time",IF(A2-B2>TIME(0,6,0),"Early","Late"))

 

[NewbieToNerding]

NtN,

I only used 6 minutes in the below formula. I.e. If the actual schedule is within 6 minutes (early or late) it's "On Time". If it is greater than 6 minutes on either end it's either "early" or "late". Column A contains Posted Schedule, Column B is Actual.

=IF(ABS(A2-B2)<=TIME(0,6,0),"On Time",IF(A2-B2>TIME(0,6,0),"Early","Late"))

Thank you NNFM! This is perfect - working great for my needs.

Funny, my original post didn't paste my entire formula. Guess I picked the right username...

 

[Joe4]

Funny, my original post didn't paste my entire formula.

See here for the reason why it was cut off: https://www.mrexcel.com/forum/about-board/680917-formulas-not-appearing-completely-read.html

 

[NewbieToNerding]

See here for the reason why it was cut off: https://www.mrexcel.com/forum/about-board/680917-formulas-not-appearing-completely-read.html

Thanks, Joe! Very helpful.