3

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(D3), MINUTE(D3), SECOND(D3))>F3,TIME(HOUR(D3), MINUTE(D3), SECOND(D3))<G3),"Yes","No")

  • -15min (Time Scheduled minus 15 minutes)

    =IF(OR(C3=TIME(0,0,0),C3=TIME(0,1,0)),TIME(23,59,0),C3)-TIME(0,15,0)+IF(C3=TIME(0,0,0),TIME(0,1,0),TIME(0,0,0))+IF(C3=TIME(0,1,0),TIME(0,2,0),TIME(0,0,0))

  • +15min (Time Scheduled plus 15 minutes)

    =C3+"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?

Example of File

I'm using MS Office 2013.

UPDATE

Other Criteria:

  • The "Schedule Time" is the time that the backup is executed on whatever day it is run, so it can't include a date as it is true for all dates.
  • Backups are sometimes executed before the scheduled time. This is okay, so long as this is within 15 minutes of the scheduled time.

I've realized that I need to slightly change my sheet to look like the following with the dates of the month extending to the right as below and I will remove the transaction date also, as the transaction will appear under the relevant date. However, please use the above for the purpose of the examples (the image below is provided to help understand what we need to accommodate).

enter image description here

1

You can do it like this:

=IF(OR(ABS(D3-INT(D3)-(C3-INT(C3)))<TIME(0,15,0),ABS(D3-INT(D3)-(C3-INT(C3))-1)<TIME(0,15,0),ABS(D3-INT(D3)-(C3-INT(C3))+1)<TIME(0,15,0)),"Yes","No")

OR is there to test whether the actual time refers to day before or after. It's needed to work for these margin cases.

Note that C3+1 means whatever time in C3 + 1 day. This works because excel stores date & time the same way, as a number of days from 1/1/1900. The decimals are actually time stored as a fraction of a day. If you enter some time, say 12:00, it will get stored as a number 0.5. Same time on 1/2/1900 would be 2.5.

It could give you false positives, though, if your actual backup is late or early for a duration between 23h45m and 24h15m.

|improve this answer|||||
  • I substituted your formula but unfortunately, the behavior for this row is exactly the same as mine and that's what I'm trying to overcome. This backup was compliant but was marked as non-compliant. Good try though. Thanks. – Peter Snow Jun 15 '16 at 8:25
  • Huh, that's strange. For me it works flawlessly (Excel2010, though - imgur.com/Y8DI1AM). How is your time stored there, as text maybe? – JollyMort Jun 15 '16 at 9:07
  • Time is stored as "Time 13:30" format sir. Just tried it again. Same result. Marking row 3 as non compliant. – Peter Snow Jun 16 '16 at 7:37
  • Ah, I saw in other comments you had the date stored in there as well. Now fixed it by taking only the time from the stored value. Try again please, should work now. – JollyMort Jun 16 '16 at 10:16
  • Yes Siree! This has worked. Thanks for your help :) – Peter Snow Jun 17 '16 at 10:04
1

In my example, column A to D contain the same data as in your example. Column E, F and G are then filled with the formulas underneath.

You could add two columns with a date/time stamp. One for the schedule time which is in your case (column E):

=A2+C2

And one which is the start time (column F).

=IF(D2<C2-TIME(0,15,0),A2+1+D2,A2+D2)

This formula checks if the time in start time is smaller than the time in scheduled minus 15 min (so next day and if it is more than 15 min earlier it is non compliant either way) and than adds one day to time date stamp.

Than you can just check whether it is compliant with this formula (column G):

IF(AND(F2<E2+TIME(0,15,0),F2>=E2),"Compliant","Not compliant") 

Edit: Reading all your comments earlier the start time and scheduled time are date time objects so something simple like this should work.

=IF(AND(D2>C2-TIME(0,15,0),D2<C2+TIME(0,15,0)),"Compliant","Non compliant")
|improve this answer|||||
  • It was looking good sir, until I read that you're supposing that the backups never start before the scheduled time. Unfortunately, I'm having to accommodate this too, looking at the raw data downloaded. – Peter Snow Jun 15 '16 at 6:08
  • Sir, please can you align your columns with those in the sample? Or if not, please tell me what column "A2" is referring to, as I'll need to update it. Same with the other columns referred to in your formulas. – Peter Snow Jun 15 '16 at 8:39
  • I can't make the schedule time column a date/time column as the schedule is every time when run. Actually, I just realized that I will also need to remove the Transaction Date column too because of this as many backups are daily and I intend to expand the sheet to the right, to accommodate the actual run times of each day in a month.(for each day I would have 4 columns (Start Time, Compliant, -15min and +15min). – Peter Snow Jun 16 '16 at 7:33
  • I'll try to come up with an easier solution. Can you put all this extra information also in your question? – Michthan Jun 17 '16 at 5:45
  • Okay thanks. I've updated my question to make it clearer. – Peter Snow Jun 17 '16 at 9:59
0

I'm thinking it can't be done with how you have it set up currently. Just using the TIME functions, Excel can't differentiate between yesterday and today. In the background TIME goes from 0 at midnight to .99999999 at 11:59:59 pm. It doesn't rollover to 1.0000, 1.0001, etc. to the best of my knowledge.

If you can pull the full time stamp into your report, then your formula can be adjusted to realize that 15 minutes prior could actually be part of yesterday, or 15 minutes later be in a future date.

|improve this answer|||||
  • Thanks. The full time is imported for the "Start Time". It's "6/1/2016 12:01:00 AM" but not displayed because the field is formatted as a "Time 13:30" field, but it's displayed when you click on it. Maybe I need to have this translated into the plus & minus fields as well. I'll see if I can do that. – Peter Snow Jun 15 '16 at 5:54
0

you have to consider the date column.

A: Transaction Date
C: Schedule
D: Start time
E: Compliant

-15 Min column (F):  =A6+C6-TIME(0,15,0)  
+15 Min column (G):  =A6+C6+TIME(0,15,0)

Compliant column (E):   =--AND(A6+D6>=F6,A6+D6<=G6)  

Result for compliant: (0 = No, 1 = Yes)

you could also do it without the -/+ 15 Min column:

=--AND(A6+D6>=A6+C6-TIME(0,15,0),A6+D6<=A6+C6+TIME(0,15,0))
|improve this answer|||||
  • Sir, Column "B" is the tape label. Is that what you were refering to in "B6"? Also when entering "Compliant" formula I get an error. – Peter Snow Jun 15 '16 at 6:05
  • see the corrected code considering the tape Label column as B. The result is boolean: 0 = False, 1 = True – visu-l Jun 15 '16 at 6:34
  • Hi Sir, I tried both of your solutions. In both cases, the result is zero (not compliant) but it should be 1 for the example shown in my post above (row 3). – Peter Snow Jun 16 '16 at 7:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.