I am given a list of equipment outages that contain the EVENT_TIME & END_TIME (event durations are recorded if they are greater than 3 min and above, but can last for days).
This equipment has two modes of opperation "day" [07:00>19:00] & "Night" [19:00>07:00]. (The night mode spans two dates).
I have to calculate the day, night and total duration in "outage minutes" either MS Excel or Access. Has anyone done this or similar calculations out there, and wishes to share their clearly superior acumen please respond.
Raw data is csv in Euro date format (swap if U like)
I have done this for you, you may want to check that itr works ok, i've had a quick check and it seems to be working. ANy problems post back here. Cut and paste this into a module, you can pass in the data, see comments.
Dim dtStart As Date 'Stores Start Date
Dim dtEnd As Date 'Stores End Date
Dim lnDayCount As Long
Dim lnNightCount As Long
dtStart = CDate("23/07/2003 18:59") 'These values could be passed into this sub
dtEnd = CDate("23/07/2003 19:03") 'These values could be passed into this sub
Do While dtStart <= dtEnd = True
'Work out if the current datetime should be classed as day or night
If Format(dtStart, "hh:nn") >= CDate("07:00") And Format(dtStart, "hh:nn") < CDate("19:00") Then
lnDayCount = lnDayCount + 1
lnNightCount = lnNightCount + 1
dtStart = DateAdd("n", 1, dtStart) 'Add 1 minute
'Store the rewsults wherever you need them
Debug.Print lnDayCount & "m Day, " & lnNightCount & "m Night, " & lnDayCount + lnNightCount & "m Total"