# Thread: Time duration calculations

1. Registered User
Join Date
Aug 2003
Posts
5

## Unanswered: Time duration calculations

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

2. Registered User
Join Date
Jul 2003
Posts
48

## Re: Time duration calculations

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.

Sub WorkoutMins()
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
'Day
lnDayCount = lnDayCount + 1
Else
'Night
lnNightCount = lnNightCount + 1
End If
dtStart = DateAdd("n", 1, dtStart) 'Add 1 minute
Loop
'Store the rewsults wherever you need them
Debug.Print lnDayCount & "m Day, " & lnNightCount & "m Night, " & lnDayCount + lnNightCount & "m Total"
End Sub

3. Registered User
Join Date
Aug 2003
Posts
5
Cheers Bscorer

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•