My Assistant
Custom Search

How To Calculate Time Interval Between Two Dates, Access 2010 
Jun 23 2019, 03:25 PM Post#1  
Posts: 11 Joined: 22March 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: 20170720 EndDate: 20171009 Result: 3 month(s) (with dateDiff but in fact it should be: 2 month(s) 19 day(s)) 20121001 20180119 > 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. 
Jun 23 2019, 03:30 PM Post#2  
UA Moderator Posts: 78,617 Joined: 19June 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 
Jun 23 2019, 03:38 PM Post#3  
UA Admin Posts: 37,633 Joined: 20June 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 did business for 20 years. How to Ask a Good Question Beginning SQL Server 
Jun 23 2019, 03:39 PM Post#4  
UA Admin Posts: 37,633 Joined: 20June 02 From: Newcastle, WA  And theDBGuy has provided the link already.  My Real Name Is George. Grover Park Consulting is where I did business for 20 years. How to Ask a Good Question Beginning SQL Server 
Jun 23 2019, 04:57 PM Post#5  
Posts: 3,095 Joined: 4February 07 From: USA, Florida, Delray Beach 
 
Custom Search

Search Top LoFi  6th August 2020  11:11 AM 