# Thread: Calculating time difference and Median or Mean off times

1. Registered User
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?

2. Registered User
Join Date
Apr 2014
Location
Kentucky
Posts
611
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. Registered User
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.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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

Join Date
Feb 2004
Location
New Zealand
Posts
1,477
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