time duration calcs [Archive]

brickwalls

08-26-03, 22:22

Hi all,
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)

eg1,23/07/2003 08:12,23/07/2003 08:59
eg2,23/07/2003 12:22,23/07/2003 03:59
eg3,24/07/2003 12:00,01/08/2003 17:00
eg4,25/07/2003 01:00,27/07/2003 07:20

eg1 => 47m day, 0m night, 47m total
eg2 => 398m day, 539m night, 937m total

Hvae considered a lookup command to a table that contains the standard durations "between" the excess bits of number of full day & night outages ie:eg3 has 6 full days outage + bits on either end.

eg3 excess of 420min day + (8*720min night) + (7*720min day) + excess 600min of day = 11820

Dcutler

09-05-03, 17:50

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.