UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Calculate Time Interval Between Two Dates, Access 2010    
 
   
JanRaven
post Jun 23 2019, 03:25 PM
Post#1



Posts: 9
Joined: 22-March 19



Hi everyone.

I've got a subform with two columns: StartDate and EndDate. I would like to add a third column and in Control source in Expression builder I want to enter a formula that would calculate time interval.
I tried the 'DateDiff' function but it just does not cut it and here is why:

StartDate: 2017-07-20
EndDate: 2017-10-09
Result: 3 month(s) (with dateDiff but in fact it should be: 2 month(s) 19 day(s))

2012-10-01 2018-01-19 -----> I would like to have a formula that would output the following result: 5 year(s) 3 month(s) 18 day(s) between the two dates.

Any help greatly appreciated.
Go to the top of the page
 
theDBguy
post Jun 23 2019, 03:30 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,698
Joined: 19-June 07
From: SunnySandyEggo


Hi. Check out this custom function: Diff2Dates().

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Jun 23 2019, 03:38 PM
Post#3


UA Admin
Posts: 35,290
Joined: 20-June 02
From: Newcastle, WA


DateDiff() returns the interval between two dates depending on which period you specify. That could be the difference in years, the difference in months, the difference in days, the difference in hours, the difference in minutes, or even the difference in seconds.

The problem here appears to be that you are asking for the difference in months, but what you REALLY want is the difference in YEARS, MONTHS and DAYS, i.e. a combination of three different intervals, not just one.

So, you need to get creative and start with a calculation that returns the number of days, and then figure out from that result how many whole years that interval represents, PUS how months left over months that interval represents, PLUS how many days are left over. In other words, 2 months, and a left over amount of 19 days. Or 5 years, and a left over amount of 3 more whole months, and a left over amount of 18 more days.

This would be a lot easier, of course, if all months had the same number of days (so you could divide the total number of days in the interval by 30), but that's not the way our calendar works. So, you'll have to use some additional logic to figure this out.

Does that help understand the situation?

I think there are probably a couple of different ways to get where you want to go, but they'll mostly involve a VBA function, I think I have something, which I'll go look for, and in the meantime, someone might have one handy.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Jun 23 2019, 03:39 PM
Post#4


UA Admin
Posts: 35,290
Joined: 20-June 02
From: Newcastle, WA


And theDBGuy has provided the link already. thanks.gif

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ADezii
post Jun 23 2019, 04:57 PM
Post#5



Posts: 2,455
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. If you are looking for something a little more simplistic but may not be 100% accurate in all cases:
  2. Sample Data (tblTest):
    StartDateEndDate
    10/1/20121/19/2018
    7/20/201710/9/2017
    1/1/20195/28/2019
  3. Create the following SQL Squery:
    SQL
    SELECT tblTest.StartDate, tblTest.EndDate, fCalcDateDiff([StartDate],[EndDate]) AS Time_Interval
    FROM tblTest;
  4. Function Definition:
    CODE
    Public Function fCalcDateDiff(dteStart As Date, dteEnd As Date) As String
    Dim intNumOfDaysDiff As Integer
    Dim intNumOfYears As Integer
    Dim intNumOfMonths As Integer
    Dim intNumOfDays As Integer

    intNumOfDaysDiff = DateDiff("d", dteStart, dteEnd)

    intNumOfYears = Int(intNumOfDaysDiff / 365.25)
    intNumOfMonths = Int((intNumOfDaysDiff - (intNumOfYears * 365.25)) / 30)
    intNumOfDays = Int(intNumOfDaysDiff - ((intNumOfYears * 365.25) + (intNumOfMonths * 30))) - 1

    fCalcDateDiff = intNumOfYears & " year(s) | " & intNumOfMonths & _
                    " month(s) | " & intNumOfDays & " day(s)"
    End Function
  5. OUTPUT from Query:
    StartDateEndDateTime_Interval
    10/1/20121/19/20185 year(s) | 3 month(s) | 18 day(s)
    7/20/201710/9/20170 year(s) | 2 month(s) | 20 day(s)
    1/1/20195/28/20190 year(s) | 4 month(s) | 26 day(s)


Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th July 2019 - 05:09 PM