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

x
?
Solved

Days Elapsed and Hours Elapsed in Access 2013

Posted on 2015-01-21
3
Medium Priority
?
198 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
3 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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 40

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 52

Expert Comment

by:Gustav Brock
ID: 40562808
Try this:

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

/gustav
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

581 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