Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

Access 2007

I have table with 80 000 records and two dates, want to see the difference in hours, in this format "20.24" with the ".24"
and instead now results only show "20.00"

Dates are in this format "2/14/2014 6:27:37 PM"

and if I use this code:


I get results like this 20.00
Notice: no "tens and hundreds of the hour" after the "."

Any help, appreciated!
My Property for Result of Calculation is Format:Standard
4 Solutions
DateDiff() rounds to the unit so you will never get fractions.  To do what you want you need to get the difference in minutes.  Then divide the minutes by 60.  that will give you a result like 21.5 for 21 and a half hours.  If you want it to be 21:30 then you have to do a truncated divide and use the remainder as minutes.  So instead of doing SomeField / 60, it is SomeField \ 60 with a backslash instead of a forward slash.  Then you subtract the result from SomeField to get the remainder and that is the minutes.

Difference: DateDiff("n",[OPENEDDATETIME],[CLOSEDDATETIME]) / 60
Rey Obrero (Capricorn1)Commented:
Gustav BrockCIOCommented:
If you want it rounded exactly by 4/5 to the closest hundreds of an hour, you can use Format:

HoursCount: CDbl(Format(([CLOSEDDATETIME] - [OPENEDDATETIME]) * 24, "0.00"))

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Your expression:

Modify to: You need the format in PatHartman's comment to limit to 2 decimal places.
Expr1: Format(DateDiff("n",[OPENEDDATETIME],[CLOSEDDATETIME])/60, "0.00")
gustav's comment produces the same result, assuming the subtraction defaults to days.
Gustav BrockCIOCommented:
> .. assuming the subtraction defaults to days.

Not quite sure if you mean it won't work for small differences?

My simple expression will work for any (realistic) time difference.
It will round up or down with a maximum of 18 seconds which matches 1/100 hour.

Subtracting one date from another defaults to day as the units so DateA - DateB = number of days.  To get any other units, you must use the DateDiff() function.
Gustav BrockCIOCommented:
Pat, why do you think so? Date/time is not integers but doubles.


? CDate(#2014-10-06 14:52:27# - #2014-10-06 08:10:07#)
=> 06:42:20

I know that dates are doubles.

Look what happens if the dates span 1 day.
? cdate(now() - #10/6/14 10:00#)
12/31/1899 5:31:31 AM
And this is two days
? cdate(now() - #10/5/14 10:00#)
1/1/1900 5:33:37 AM
This is two days without reformatting as a date.
? (now() - #10/5/14 10:00#)
And look at this strange one.  The first is 3 hour difference but the second is 15
? cdate(#10/7/14 01:00 AM# - #10/6/14 10:00 PM#)
3:00:00 AM
? cdate(#10/7/14 01:00 AM# - #10/6/14 10:00 AM#)
3:00:00 PM

The reason is because Access is calculating the difference in days with a remainder.  You can't ever get actual hours and the format of the result depends on whether you are using AM/PM or 24-hour time.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now