asked on

Days Elapsed and Hours Elapsed in Access 2013

The Facts:

I have some Tables that I am not allowed to touch as far changing data types or data in it. I need to create a query that another data base is going to be pulling from.

I have two columns that have the DataType set as Numbers using the m/dd/yyy h:nn format (not date/time)

I need to take the difference of these columns and show as two new columns
DaysElapsed (needs to be in decimals - like 1.23 days)
HoursElapsed (needs to show more than 24 hours, so if it took an exact day and one half, the number would 36

My end goal is that I am working in another system that needs these showing in the above format.

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PatHartman

the Date/time data type is actually a double precision number with the integerportion representing the number of days since 12/30/1899 and the decimal being the time since midnight. Negative numbers are for dates prior to 12/30/1899. So for today:
print now()
1/21/2015 11:45:49 AM
print cdbl(now())
42025.4902430556

So, you say the field is a number but it is formatted as a date. Does that mean it works like a real date/time field? We need to know the meaning of the parts in order to know how to do the calculation.

Can you provide some data samples showing both the native value and the formatted value?

Gustav Brock

Try this:

DaysElapsed: DateEnd-DateStart
HoursElapsed: DateEnd-DateStart * 24

/gustav