In Excel 2013, need formula to calculate years, months, & days between 2 dates, 1 or more pre-1900

Question

In Excel 2013, I need to create formula to calculate years, months and days between two dates, if one or both dates is pre-1900.
For example: 5/20/1896 - 12/11/2004 = 108 years, 6 months, 21 days,
or 7/19/1757 - 3/17/1858 = 100 years, 7 months, 26 days.

Answer 1

To implement Máté Juhász's suggestion

Place the dates (as strings) in cells A1 and A2. In B1 enter:

=DATEVALUE(LEFT(A1,LEN(A1)-4) & RIGHT(A1,4)+2000)

and copy downward. Then in another cell, enter Chip Pearson's formula:

=DATEDIF(B2,B1,"y") & " years, " & DATEDIF(B2,B1,"ym") & " months, " & DATEDIF(B2,B1,"md") & " days"

To use VBA:

Consider the following User Defined Function (UDF):

Public Function ddif(d1 As Date, d2 As Date) As String
    Dim years As Long, months As Long, days As Long

    years = 0
    months = 0
    days = 0
    year1 = Year(d1)
    month1 = Month(d1)
    day1 = Day(d1)

    Do
        If DateSerial(year1 + years, month1, day1) = d2 Then
            GoTo finished
        End If
        If DateSerial(year1 + years, month1, day1) > d2 Then
            years = years - 1
            Exit Do
        End If
        years = years + 1
    Loop
    Do
        If DateSerial(year1 + years, month1 + months, day1) = d2 Then
            GoTo finished
        End If
        If DateSerial(year1 + years, month1 + months, day1) > d2 Then
            months = months - 1
            Exit Do
        End If
        months = months + 1
    Loop
    Do
        If DateSerial(year1 + years, month1 + months, day1 + days) = d2 Then
            GoTo finished
        End If
        days = days + 1
    Loop
finished:
    ddif = years & "  " & months & "  " & days
End Function

The years, months, and days will be returned with a single space between them.

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=myfunction(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

Answer 2

Thank you so much for your reply Gary, I do know this is an old post but please everyone consider this.

I spent many hours searching on Google and try VBA codes to nothing, but this is finally the very correct way in VBA to get how many years, months and days between two dates regardless of the Excel's problem with 1900's anterior format.

This way you can easily and very concise, know the correct interval without approximation and complex lines with bissextiles, number of days in a month changes..

I apologize for the late reply, but everyone with old dates issues on excel should look here and finally get something working and precise. Thank you.

Please look at VBA code, unfortunally DATEVALUE didn't work for me.