Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2015
    Posts
    2

    Unanswered: Calculating time difference and Median or Mean off times

    Hi-
    I have a database where our users enter times for ambulance calls. Some of these time cross midnight. These times are entered as short times. In a query I am then calclating the time difference to see for instance how fast they get to a call. I am using:
    Format([time1]-1-[time2],"short time")

    Now that I have that I am struggling with calculating a mean or median because the time is short time format. I was using totals with datediff() but that was not working with times crossing midnight. Is there a way to calculate off of the short time or convert a short time to minutes?
    Thanks in advance

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    591
    Provided Answers: 30
    A date field holds BOTH date and time. It should DATEDIFF correctly.
    If you are not storing the date in that field, that is a design flaw.
    Add Date to the time and it will calc correctly.

  3. #3
    Join Date
    Apr 2015
    Posts
    2
    The time is being correctly calculated, however when I try and run totals/average it wi not run them on a time. I guess my question should have been how to convert the times to minutes by adding the the above formula.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how are you storing these values?
    by 'short times' I guess you mean you are just putting int he time element of a DATEtime value.
    ...as ranman suggests either sotre the dsate and time

    or you need to be a bit smarter on handling temporal data.
    you could use an IIF to handle this, somethign like:-
    iif(timeofcall > timeofarrival, datediff("n",dateadd("d",1,timeofarrival), timeofcall, datediff("n",timeofarrival, timeofcall)
    in essence test if the time the call arrived is greater than the time of attendance
    if it is then add 1 whole day to the timeofarrival
    ..then in boith cases subtract the differnece in minutes.

    you may need to tinker with this. it might (however dodgy/dirty workaround) but simp0ly subtract the two values and multiply by 1 / (24*60).
    Access/JET stores time values as the proportion of a day, so one minute is 1/(24[hours in day] * 60 [minutes in hour]
    ...the integer part is the day number (since, IIRC 31/12/1899)

    it may be smarter to do this in a function rather than an IIF
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,475
    Provided Answers: 11
    what i would do and have done

    Work out the minutes of each job datediff('n',[date1],[date2])

    Then you can add the minutes get a total minutes or avg minutes or what ever minutes you want by there groupings

    KNOW here a bit of code that convert minTohrs and display in HH:MM format

    Code:
    Function Mintohrs(Tmin)
        hh = Int(Tmin / 60)
        tt = hh * 60
        mm = Tmin - tt
        Mintohrs = DIG(hh) & ":" & DIG(mm)
    End Function
    
    Function DIG(num)
         if len(num)<>2 then
            DIG ="0" & num 
         else
            DIG = num 
         end if
    End Function
    job1 = 90 min
    job2 = 90 min
    job2 = 15 min

    total jobs = 195 min
    avg jobs = 65 mins
    so
    03:15 = Mintohrs(195)
    01:05 = Mintohrs(65)

    in a query

    Mintohrs: Mintohrs([feildnametotalminsvalue])

    or in a report

    =Mintohrs([feildnametotalminsvalue])
    Last edited by myle; 04-10-15 at 00:08. Reason: spelling
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

Tags for this Thread

Posting Permissions

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