# Thread: Calculating Time in Hours/Minutes

1. Registered User
Join Date
Mar 2004
Posts
287

## Unanswered: Calculating Time in Hours/Minutes

Here's my next problem guys...

I want to calculate the time taken between Received and Closed (both are general date fields including the time 00:00:00)

I am using the following code;
Code:
`[TimeTaken] = DateDiff("h", Received, Closed)`
I can work out the hours if I have TimeTaken field as a number field but how would I allow it to display the difference in time (hh:mm)?

And yes I have tried the following:
[TimeTaken] = DateDiff("hh:mm", Received, Closed) (nor h,n h:n hh:nn)

but that doesn't work?

Also, what type should the TimeTaken field be - number or date field

Thanks.
Last edited by NeilMansell; 04-17-07 at 07:10.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
access stores date time values as integer=day,
decimal=fraction of a day
a second is 1/(24*60*60) = 0.00001157407407...... ish
an hour is 1/(24*60) = 0.0416666667 ish
so if you simply subtract the two values you have the time difference between the two events. this wokrs if the event duration staggers over more that one day

use format (with an appropriate mask) to display the results
the potentail problem with format is if you have more than 24 hours...

3. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
804
Hi

Assuming Received & Closed have a day part and can be different days then Time Taken is stored as this (does it need it be stored ?).
[TimeTaken] = Closed - Received (as number/single)

and displayed with this

Int([TimeTaken]) & Format(([TimeTaken]) - Int([TimeTaken]), ":hh:mm:ss")

This will display time taken in d:hh:mm:ss format.

Does that help

MTB

4. Registered User
Join Date
Mar 2004
Posts
287
many thanks. followed your instructions and all works fine!

5. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Code:
```TimeTaken = DateDiff("h", Received, Closed)

NumberOfHours = Int(TimeTaken / 60 )
NumberOfMinutes =  (TimeTaken % 60)
'is it % or MOD in Access?
Output = NumberOfHours & ":" & NumberOfMinutes```