Solved

Days Elapsed and Hours Elapsed in Access 2013

Posted on 2015-01-21
3
186 Views
Last Modified: 2015-01-26
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.
0
Comment
Question by:rnhturner1
[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
  • Learn & ask questions
3 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40562245
If the "Numbers" are legitimate Date values, then you can use CDate to convert them to a Date value, then use standard Date functions to determine the differences.

So you could do something like this:

DaysElapsed: DateDiff("h", CDate(Col1), CDate(Col2)) / 24

Note you must use the "forward slash" ( / ) and not the "backward slash ( \ ) to get the right result.

HoursElapsed: DateDiff("h", cDate(Col1), CDate(Col2))

Here's the MSDN entry on DateDiff:

https://support.office.com/en-US/Article/DateDiff-Function-e6dd7ee6-3d01-4531-905c-e24fc238f85f?ui=en-US&rs=en-001&ad=US
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 40562271
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?
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40562808
Try this:

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

/gustav
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question