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

Improve this question

4 Answers 4

Reset to default
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
5
  • 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 White
    Jun 15, 2016 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, 2016 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 White
    Jun 16, 2016 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, 2016 at 10:16
  • Yes Siree! This has worked. Thanks for your help :)
    – Peter White
    Jun 17, 2016 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
5
  • 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 White
    Jun 15, 2016 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 White
    Jun 15, 2016 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 White
    Jun 16, 2016 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, 2016 at 5:45
  • Okay thanks. I've updated my question to make it clearer.
    – Peter White
    Jun 17, 2016 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
1
  • 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 White
    Jun 15, 2016 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
3
  • 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 White
    Jun 15, 2016 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, 2016 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 White
    Jun 16, 2016 at 7:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .