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)
If you import the data to Excel, a simple formula in Column D of + Col B - Col C will give you the difference in the form of a serial value. Col A would be the eq #, Col B the Event_Time and Col C the End_Time. The use custom cell formatting to get it in minute form, like this: [mm];@. Excel can import a csv file easily, just remember to format the two date columns as dates, not text when running the "Text Import Wizard". You could also use MS -Query within Excel via an OBDC to download the data from the db directly to excel without creating an intermediary csv text file.