Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Days Elapsed and Hours Elapsed in Access 2013

Posted on 2015-01-21
3
Medium Priority
?
192 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 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 39

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 51

Expert Comment

by:Gustav Brock
ID: 40562808
Try this:

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

/gustav
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

722 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