Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2016

    Unanswered: Using Times (start & end) to calculate how many seconds fall into each HR of DAY

    Ok, I'm new here but have decent experience with Excel and Database type systems. This one has me stumped, and i'm tired of racking my brain to find the cleanest way to accomplish the task.

    Here is issue. I'm in the fire department. We track data on how long a fire truck is out of service on any one date, and the range (in seconds) that it is out-of-service. The crappy municipal program that collects the data shows raw data in the incorrect format for accurate tracking.

    The data shows catergory heads in this order:

    Unit ID / Status / Unit History Time / Hour of Day / Day of week / Week of Year / Month / 168 hour / Status in Seconds / Hours at Status

    Unit ID - is the name of the fire truck
    Status - is Out of service, on a detail, unavailable for staffing, etc
    Unit History Time - the exact time the status started
    Hour of Day - the hour of the day that the status started (0-23)
    Day of Week - number of the day of the week starting on Sunday (1-7)
    Week of the year - numeric for corresponding calendar week (1-52)
    Month - Numeric value - (1-12)
    168 hour - The numeric of the number of hours in a full week (168 hours in a week), numeric represents where within that 168 it lies.
    Status in Seconds - the number of seconds after the "Unit History Time" stopped. So if a status lasted a minute from 09:00 to 09:01, it would read 60.
    Hours in Status - the number of hours after the "Unit History Time" stopped. Recorded down to the hundredth decimal (e.g. 1.45)

    What I'm trying to accomplish is multi-fold.

    I have taken the "Unit History Time" and added formula =C2+TIME(0,0,I2) where C2 was the "Unit History Time and I2 was the Seconds in Status. That has spit me out a new date and time correlating to the seconds added to the start time.

    Here's where the trickiest part comes in:

    I need to be able to somehow take that information in some form and create an equation that will read that start time (let's start with 12/1/16 08:20:28 as Unit History Time), and calculate the amount of "Seconds in Status" (2399) and it spits out in another column 12/1/16 9:00:27, and decide:

    "Is this time frame between 08:00:00 and 08:59:59, and if so put the appropriate number of seconds of that hour in that column.

    So I have numbered columns all the way across like 0 / 1 / 2 / 3 / 4 / 5 / 6 / 7 / 8 / 9 / 10/ etc. through 23.

    I need it to drop the numeric for how many seconds in that #8 column of that line of data. I would assume I need an equation for each time range in that 0-23 range that reflects the hour range but the framework would be the same looking at the fixed columns of "Unit History Time" (C), "Seconds in Status" (I), and "Time back in service" (L).

    Another example would be C3 = 1/24/16 09:33:34 I3 = 5849 L3 = (the equation formula output) 1/24/16 11:11:03

    With this string of data, I need the equation(s) to drop 1586 seconds into the "9" column, 3600 into the "10" column, and 663 in the "11" column for the line of data.

    So it would read across my screen as:

    0 / 1 / 2 / 3 / 4 / 5 / 6 / 7 / 8 / 9 / 10 / 11 / 12 / 13 / 14 / 15 / 16 / 17 / 18 / 19 / 20 / 21 / 22 / 23

    0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 1586 / 3600 / 633 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 / 0

    I have been struggling for a couple days to determine the most effective way to make this work and am hoping others can point me in the right direction with some formula or thought process help here.

    This data stream at the end will all my company to effectively evaluate how many seconds our fire truck was unavailable for calls mechanical (OOS) and begin to see if there are trends in the data of when things are happening most frequently at what times of the day. The date comes into play when analyzing the time of the year or month that increases in timeframes occur.

    Currently the data that is provided for graphing calculates out of service time that starts at 07:00 and lasts to 10:00 as 3 hours (or 10,800 seconds) under the "10:00" hour when it actually spanned across the 7, 8, 9, and 10 hour frames. So when plotting the data it looks as thought 07:00 has been OOS for 3 entire 60 minute time frames in the "7" time bracket, over the 365 day year, instead of 3 hours over 1 day from 7, 8, 9, and ending at 10.

    Any help is greatly appreciated.

    Here is a link to a sample of the information i'm talking about:
    Last edited by MHAGGERTY4; 08-21-16 at 00:35.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    Do it as a function.

    the call would be something like
    public function CalcOutOfService(OffServiceas date, OnService as date,ReportHour as integer) as variant this simple, first cut you would have to supply the offservice and onservice valuesas a datetime value
    the report hour you'd specify (as an integer in range 0 to 23) to indicate the hour you want to analyse.
    but there's a fair bit of work to write the actual function.

    for me, unless Im adding the data any user written function needs to thoroughly validate the data make the values are of the right type, the right range, supply defaults if defaults are sensible.
    do the processing
    return a value

    once the function is written you'd call it in each of the 24 analysis columns
    say the
    =CalcOutOfService(my, parameter, list)

    but your data isn't in the template function form.
    so you have various choices
    you can convert from your source data using say hidden columns. do soem calcualtaions tht simplify things in a column or sheet not within the current printable area.
    Or pass the whole mess to your function and let it sort out if the values are in range, do whatever is required.

    so in your case
    you do have an event start date and time
    you have an elapsed seconds, so from that the function could work out the end date and time does beg the question what happens if an event starts at, say 23:35 and and takes 7200 seconds (ie ends 02:35 the following day. do you need to handle that here or is it handled int he municipal program...

    so the function will probably take the form
    public function calcOOSSeconds(EventStart as variant, Duration as variant, ReportHour as variant) as variant
    '.... the reason for using variant is that a variant will accept any value, even the wrong type or missing data.
    'EventStart should be the date and time an event started
    'Duration is the number of seconds from the eventstart for this event
    'ReportHour is a integer in range 0..23 which indicates the hour we watn to report on
    'the function calculates the number of seconds this device was Out of Service in the specified report hour
    calcOOSSeconds = null 'set up our  defualt return value. we will use null, but could just as equally be ""
    'so lets do some validations
    'the eventstart MUST be a date
    if  isdate(eventstart) = vbfalse then 'the user is 'aving a larf, its not a date, its a fatal error
      exit function
    'the event duration must be in a specific range 0..86400, anythign else is a fatal error
    if isnumber(duration) and (duration <0 or duration > 86400) then 'again the user is 'aving a larf
      exit function
    'the reporthour must be in range 0..23, anythign else is fatal
    if isnumber(reporthour) and (reporthour <0 or  reporthour>23) then then 'again the user is 'aving a larf
      exit function
    'ok so we got to here we think the data is valid
    Dim StartDate as date 'will be the datetime the event started
    dim LowerLimit as date ' is the start of our reporting period
    dim Upperlimit as date ' is the end of our reporting period
    dim enddate as date 'is the datetime when the event ends
    Startdate = cdate(eventstart)'so that now gives us the start of the event as a datetime value
    'so lets work out our event end time
    enddate = dateadd(DateInterval.Second, duration, startdate) 'so we know have the event end as a datetime.
    'we only care if the this event falls within our report hour. so
    LowerLimit = dateadd(DateInterval.Hour, reporthour,startdate) 
    upperlimit = dateadd(DateInterval.Second, 3599, lowerlimit)
    'so if the eventstartdate was 21 April 2016 18:24:35 and the report hour is 20... then
    'we should have a LowerLimit of 21 April 2016 20:00:00
    if enddate < lowerlimit then ' the event ended before our reporting period 
      exit function
    if startdate >= upperlimit then ' this event started AFTER our report period
      exit function
    'so now the fun and games start. we know this event falls within our reporting period
    if startdate >= lowerlimt then 'the event started in this hour
      if enddate > upperlimit then 'the event ended after our reporting period
        'so use the upperlimit as our cutoff
        calcOOSSeconds = datediff(DateInterval.Second, upperlimit, startdate)
      else 'the event ended BEFORE our reporting period ended
        'so use the enddate as our cutoff
        calcOOSSeconds = datediff(DateInterval.Second, enddate, startdate) 
    else ' the event started BEFORE this hour
      if enddate > upperlimit then 'the event ended after our reporting period
        'so use the upperlimit as our cutoff
        calcOOSSeconds = datediff(DateInterval.Second, upperlimit, lowerlimit)
      else 'the event ended BEFORE our reporting period ended
        'so use the enddate as our cutoff
        calcOOSSeconds = datediff(DateInterval.Second, enddate, lowerlimit) 
    exit function
    sorry I don't have a copy of excel on this device and its starting to get too fiddly to do as air code. perhaps soemone else can step in. failing that Ill have another look next weekend. this will get you where you need to be, but you will need to thoroughkky debug it it make sure its fit for purpose.

    ...personally I'd say this is something your should be doing in a database as opposed to a spreadsheet, but seeing as I spend most of days using db's I would say that
    Last edited by healdem; 08-21-16 at 06:59.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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