calculating time accurately

[james7705]

please can someone help me.


i have a opened date[default value =Now()]
and
resolved date[default value =Now()].


i would like to calculate the time elapsed between the two dates.


it is normally on the same day, but there are instances where it can run past midnight.

[pbaldy]

As suggested elsewhere, check out the DateDiff() function.

[james7705]

As suggested elsewhere, check out the DateDiff() function.

been looking all over and following links to suggestions and tips and i'm still not getting it right. i have created a query based on the table using only the time controls i.e "opened date" and "resolved date". in the field tab of the query i created a field called

Time Taken: DateDiff("h";[Opened Date];[Resolved Date])

when i create a form based on the query the time taken field gives me a number

opened date 2014-10-02 13:31:17
resolved date 2014-10-14 15:25:41
time taken 290

what have i done wrong?

[June7]

What's wrong with that number? There are 290 hours from the start to the end date/time

[james7705]

What's wrong with that number? There are 290 hours from the start to the end date/time

yes i get that.

how do i convert that to "dd, hh:nn"? and if days are zero then to show in "hh:nn"?

[June7]

Review post 9 in https://www.accessforums.net/access/...ime-38266.html

There area other threads discussing this.

[pbaldy]

I'll get out of the way.

[james7705]

Time Calculation.zip
here is a sample of what im doing.
please can anyone help?
my calculations keep coming out wrong

[June7]

Remove the formatting from the TimeTaken textbox.

Calculations in the query:

ElapsedMins: DateDiff("n",[Opened Date],[Resolved Date])

Time Taken:

Int(ElapsedMins / 1440) & ", " & Int((ElapsedMins Mod 1440)/60) & ":" & ((ElapsedMins Mod 1440)/60 - Int((ElapsedMins mod 1440)/60))*60

[james7705]

Remove the formatting from the TimeTaken textbox.

Calculations in the query:

ElapsedMins: DateDiff("n",[Opened Date],[Resolved Date])

Time Taken:

Int(ElapsedMins / 1440) & ", " & Int((ElapsedMins Mod 1440)/60) & ":" & ((ElapsedMins Mod 1440)/60 - Int((ElapsedMins mod 1440)/60))*60

thank you very much. that helps imensly. just one more question... i see my result is #, #:#,##########. i take it that it is "days, hours:minutes,seconds"
the seconds have a lot of zeros at the end. how do i shorten this?

[June7]

The result is: days, hours:decimalminutes

You can round the decimalminutes to integer if you want.