Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003

    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. #2
    Join Date
    Jul 2003

    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
    lnDayCount = lnDayCount + 1
    lnNightCount = lnNightCount + 1
    End If
    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"
    End Sub

  3. #3
    Join Date
    Aug 2003


    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