Solved

# Access 2007

Posted on 2014-10-03
188 Views
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:

Expr1: DateDiff("h",[OPENEDDATETIME],[CLOSEDDATETIME])

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
0
Question by:CABRLU63
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 37

Accepted Solution

PatHartman earned 300 total points
ID: 40360511
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
0

LVL 120

Assisted Solution

Rey Obrero (Capricorn1) earned 50 total points
ID: 40360884
0

LVL 50

Assisted Solution

Gustav Brock earned 100 total points
ID: 40360960
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"))

/gustav
0

LVL 30

Assisted Solution

hnasr earned 50 total points
ID: 40361551
Expr1: DateDiff("h",[OPENEDDATETIME],[CLOSEDDATETIME])

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.
0

LVL 50

Expert Comment

ID: 40361572
> .. 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.

/gustav
0

LVL 37

Expert Comment

ID: 40363946
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.
0

LVL 50

Expert Comment

ID: 40363961
Pat, why do you think so? Date/time is not integers but doubles.

Try:

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

/gustav
0

LVL 37

Expert Comment

ID: 40366987
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#)
2.23255787037488
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.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
###### Suggested Courses
Course of the Month5 days, 8 hours left to enroll