Solved

Days Elapsed and Hours Elapsed in Access 2013

Posted on 2015-01-21
3
182 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 36

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

756 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