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"
![enter image description here](../../I/static/images/7cd9f585ab46708233e19d0ffd8cb2b815356816d377dd5cdc83df573cfeeea5.png)
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.
![enter image description here](../../I/static/images/76261627eb00d9d3b01986c5b03dff46ec1b45d5158d4489218a88abd16b93bf.png)
User Defined Functions (UDFs) are very easy to install and use:
- ALT-F11 brings up the VBE window
- ALT-I
ALT-M opens a fresh module
- 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:
- bring up the VBE window as above
- clear the code out
- 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!
1What about just adding 2000 years to each date before the calculation? – Máté Juhász – 2017-06-25T20:05:07.513