Calculating days used to create future date 1

[gagirl43]

I create a record that has retention date and destroy date. Both dates were created by adding years, months and days to the current date. These fields (years, months and days) aren't stored anywhere. This is where my problem comes in. When the user searches for a specific record, I have to display the years, months and days used to create the retention and destroy dates. The years and months come out right for the most part, but the days don't. I inherited this code:

Function ConvertYMD(date1 As Date, date2 As Date)

Dim YFormula As Long, MFormula As Long, DFormula As Long, DTotal As Long, MTotal As Long

DTotal = Abs(DateDiff("d", date1, date2))
YFormula = Int(DTotal / 365.25)
MTotal = DTotal - (YFormula * 365.25)
MFormula = Int(MTotal / 30.4375) ' 365.25/12=30.4375
DFormula = ((MTotal - (MFormula * 30.4375)) - 1)

End Function

Date1 is 11/9/2004 and Date2 is 5/27/2007. The DFormula gives me 14 rather than 18 days. When I pass it a destroy date of 11/9/2011 as Date2, I get 6 yrs 11 mths and 28 days...not quite 7 years which is what it's suppose to be. All help is greatly appreciated.

Rhonda

 

[MichaelRed]

Without "fixing" it, I CAN tell you that the issue is the precision (or deficiency gtherein) of the values for the various units. A year is NOT 365.25 days, that is just an approximation of the average, considering that a leap year occurs ever 4th year, which is not quite trrue (the '00' years are not leapers). Since this value is "off", the Number of Months is also in-exact, and if the months are inexact, then the days MUST also be incorrect.

There is a similar (related) routine posted herein a few issues back, search for basDOB2AgeExt in these fora. It is intended to return a string with Years, Months, and Days between two dates, but should at least serve to illustrate a way to calculate the values.






MichaelRed

 

[gagirl43]

Thanks, Michael...worked like a charm!