Determining whether specified time is within 15mins of target time in Excel

I’m trying to create spreadsheet to monitor the timeliness of backups executed. Backups must be executed within 15 minutes of the target time, in order to be considered compliant. The raw data downloaded from the online monitoring application has the following relevant columns:

• Date of Transaction
• Tape Label
• Time Scheduled
• Time Executed

I’ve added the following additional columns to it:

• Compliant (Yes/No) =IF(AND(TIME(HOUR(I17), MINUTE(I17), SECOND(I17))>K17,TIME(HOUR(I17), MINUTE(I17), SECOND(I17))

• -15min (Time Scheduled minus 15 minutes) =IF(OR(H17=TIME(0,0,0),H17=TIME(0,1,0)),TIME(23,59,0),H17)-TIME(0,15,0)+IF(H17=TIME(0,0,0),TIME(0,1,0),TIME(0,0,0))+IF(H17=TIME(0,1,0),TIME(0,2,0),TIME(0,0,0))

• +15min (Time Scheduled plus 15 minutes) =H17+"0:15"

All time columns are using Time format “13:30”.

It almost works, but there is a problem when the Time Executed is “0.01” and the Time Scheduled is “0:00”. This is marked as not compliant, when it is compliant. Please can someone assist in making this work?

I'm using MS Office 2013.

Recent Questions...