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!