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

    Unanswered: Calculating time difference and Median or Mean off times

    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
    Provided Answers: 23
    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
    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
    out on a limb
    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. 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
    New Zealand
    Provided Answers: 5
    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

    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 
            DIG = num 
         end if
    End Function
    job1 = 90 min
    job2 = 90 min
    job2 = 15 min

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

    in a query

    Mintohrs: Mintohrs([feildnametotalminsvalue])

    or in a report

    Last edited by myle; 04-10-15 at 00:08. Reason: spelling
    hope this help

    See clear as mud

    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008

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