Calculating years/months/days 1

[1510982]

I have two dates. Now I want to calculate exact number of years, months and days between them. But not as 2 years, 12 months and 12*365 days, but as 4 years, 3 months and 29 days.
Isn't there any other way than DateDiff, which only returns years OR months OR days - but years AND months AND days.
To write my own function is possible, but harder if it yet exists
The biggest problem - some years have 365 days and some 366.

 

[tekola]

You can subtract one date from the other then extract Years , Months, and Days sort of like this:
DeltaDate=Date2-Date1
Years=YEAR(DeltaDate)
Months=MONTH(DeltaDate)
Days=DAY(DeltaDate)

 

[1510982]

Hmm, interesting idea. But unfortunetly it doesn't work if you subtract two dates that are close together (eg. 3.1.2002 - 5.7.2002), because it returns a stupid date that is unuseful. What to do now?

 

[MichaelRed]

Hmmmmmmmmmmmmmmmmmmmmm,

the old whine and complain option. 99% correct is not worthy of any effort on your part?

it looks quite good to me. I htink you owe tekola an apology and a star!

Dt1 = #3/2/2002#
Dt2 = #5/7/2002#

DeltaDate=Dt2-Dt1
Years=Format(DateDiff("Y", Dt1, Dt2), "yy&quot
Months=MONTH(DeltaDate)
Days=DAY(DeltaDate)
? Format(Years, "0&quot, MOnths, Days
0 3 6


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[tekola]

The subtraction of one date from another is in days, not a date. So it does work unless there is a Time tacked onto the dates being subtracted which might throw it off slightly.

 

[raskew]

1510982 (if that is your real name) --

If you subtract the greater date from the lesser date it does produce garbage with just about any system we can dream-up. The question is: "Why would you want to do that?"

Michael, in your response, you turned it around and used your logic rather than 1510982's. Look at 1510982's second example. For whatever reason he/she wanted to subtract the greater date (5.7.2002) from the lesser date (3.1.2002), and yes, it does produce crap, even with tekola's excellent response and your follow-on advice.

 

[1510982]

Thank you for all your posts. I didn't mean to be mean with the statement "returns a stupid date that is unuseful" nor sarcastic with "Hmm, interesting idea".
That was anger due those functions not working. Why? Are we born with knowledge about subtracting days and that for difference in years you actually get years+1900. Naturally I assumed it's a wrong result.
I hope this clears up my post. And it was my mistake to write that I want subtract smaller date from bigger.
I'm sorry for any inconvinience or anger I've caused. Thank you.

 

[MichaelRed]

All in all, an interesting exercise. I DID mis-read the original and there are problems with the posted result - even with the 'justification' of ordering the dates.

It is I that owe apologies. And to more than one participant in this.

Once I thoroughly reviewed the posts, I realized that there is an issue of calculating "Age" in that you need to know wheather the end 'date' is before or after the start 'date' within the interval. Thei is seen fairly clearly in the conversion of DOB to AGE, as previously discussed in these forumns. AAs a BRIEF example, assume a DOB of 6/1/2001, being calculated as of 5/31/2002 If you use DaeDiff, you would get 1 (Year) - obviously incorrect, so an additional calculation needs to check that the final date occurs before the start date within the final year (for Years). the same principal is operative for the months, so the actual calc needs to be a bit more elaborate - at least to use DATEDIFF.

The following is my (next - and hopefully last?) contribution to the subject. It appears (once again) to work.

Public Function basDateDiffYMD(Dt1 As Date, Optional Dt2 As Date = -1) As String

    'Michael Red.   2/21/2002   DateDiff as N Years, M Months and O Days

    Dim tmpAge As Integer       'Simple DateDiff w/o Birthday correction
    Dim YrsCorr As Boolean      'Yr Before or After date in question
    Dim MoStrt As Date          'DateTo Start coalulating the Months diff
    Dim MnthCorr As Boolean     'Mnth Before or After date in question
    Dim DayCorr As Boolean      'Day Before or After date in question
    Dim StDt As Date            'Earliest Date of hte Pair
    Dim EndDt As Date           'LatestDate of the Pair
    Dim YrsDif As Long          'Number of Years between (I Know "Long" is optimistic)
    Dim MnthsDif As Integer
    Dim DaysDif As Integer

    If (Dt2 = -1) Then         'Check for (Optional Date to Check against)
        Dt2 = Date             'If Not Supplied, Assume Today
    End If

    If (Dt1 > Dt2) Then         'Just for MY piece of mind only do Positive diffs
        StDt = Dt2
        EndDt = Dt1
     Else
        StDt = Dt1
        EndDt = Dt2
    End If

    YrsDif = DateDiff("YYYY", StDt, EndDt)      'Just the Years considering Jan 1, Mam
    YrsCorr = DateSerial(Year(EndDt), Month(StDt), Day(StDt)) > EndDt   'Check This Year
    YrsDif = YrsDif + YrsCorr                   'Just Years and Correction

    MoStrt = DateAdd("yyyy", YrsDif, StDt)
    MnthCorr = DateSerial(Year(EndDt), Month(EndDt), Day(MoStrt)) > EndDt   'Check This Year
    MnthsDif = DateDiff("m", StDt, EndDt) - YrsDif * 12 + MnthCorr

    DayStrt = DateAdd("m", MnthsDif, MoStrt)
    DaysDif = DateDiff("d", DayStrt, EndDt)

    basDateDiffYMD = YrsDif & " Years, " & MnthsDif & " Months and " & DaysDif & " Days."

End Function
[code]


 MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[tekola]

I tested in Excel and got this showing that you have to subtract one from the number of months, and 1900 from the number of years to get what you originally wanted.
BirthDate 6/1/01 5/4/52 1/1/02 6/1/91
Today 5/31/02 2/23/02 2/23/02 5/31/92
Diff(days) 364 18192 53 365
=Year() 1900 1949 1900 1900
=Month() 12 10 2 12
=Day() 29 21 22 30