Converting dates into months and days

[jeffreybrown]

I have two textboxes with dates and need to display months and days in another textbox.

StartDateEst = 3/7/2017
DueDateEst = 4/21/2017

=Int(([DueDateEst]-[StartDateEst])/30) & "m "

this gives me 1m

but how do I know get 1m 15d?

In Excel I would use the MOD function, but after searching and trying a few other Access suggestion, couldn't get the syntax correct.

 

[plog]

1. You are trying to subtract apples and oranges. Months don't have set amount of days, so what is the correct answer for how many months and days away is 1/31 from 2/28, or 1/31 from 3/1? What about 1/1/2016 from 1/1/2017--your method yields 12 months and 6 days.

2. I would recommend the DateDiff function for determining the difference in days between dates.

3. For your specific calculation I would recommend a custom function and do all the work there:

MonthDaysDifference: get_Difference([STartDateEst], [DueDateEst])

Then your function would get the total days and set it to a variable (TotalDays). Then you could use that to determine months and days:

Months=Int(TotalDays/30)
TotalDays = TotalDays - (Months*30)
Days = TotalDays

That middle statement simple removes all the days accounted for by whole months leaving just the remainder days.

Again though, apples and oranges. Be careful.

 

[jeffreybrown]

Thank you for the direction. I'll give this a try.

 

[missinglinq]

Doing this kind of thing...returning elapsed time in two or more components (i.e. hours & minutes or months & days) can be done as plog has demonstrated, using DateDiff, with the Interval Parameter set as the lowest denominator (minutes and days, in the above cited examples) and then parsing the results out into the desired format, as he also showed.

Doing this for Time, alone, is simple, up through days...a minute always has 60 seconds, an hour always has 60 minutes and a day always has 24 hours.

But months are something else! As plog said, they can have 28, 29, 30 or 31 days...the 'apples and oranges' he spoke of! Exactly how you do this depends on the accuracy you need in your results. He took the approach most developers take...deciding to use 30 days as the definition, if you will, of a month.

That approach is, by far, the easiest to implement...but if you need more accuracy than that provides, you're in luck! Two gentlemen by the name of Graham Seach and Doug Steele have developed A More Complete DateDiff Function that does just what you need, in a precise manner:

http://www.accessmvp.com/djsteele/Diff2Dates.html

Linq ;0)>