I need a way to subtract the difference between two dates and exprss the
difference in a format simular to 10 years 6 months 7 days. Assume a start
date of 12/19/1943 and an end date of Today(). I have no trouble using
datedif to get the number of years as 61 years, months as 11. I have now
idea of how to convert the total number of days between the two dates to the
correct number days. sngNumDays = DateDiff("d", DateOne, Now) gives the
total number of days (22657).

My current Code:

Private Sub cmdAge_Click()
Dim DateOne As Date
Dim intYears As Integer
Dim intMonths As Integer
Dim sngNumDays As Single
Dim introw As Integer
For introw = 2 To 4 '3 different dates are
entered on rows 2 through 4
DateOne = Cells(introw, 13).Value 'get the start date from
the spreadsheet
sngNumDays = DateDiff("d", DateOne, Now) 'total number of years
in the period
intYears = Int(sngNumDays / 365)
intMonths = DateDiff("m", DateOne, Now) 'months is total months
in the time period
intMonths = intMonths - (intYears * 12) 'convert to months
difference in the current year
sngNumDays = DateDiff("d", DateOne, Now) 'total number of days in
the period
Me.Unprotect
'print the results to the row and column on the spread sheet
Cells(introw, 14).Value = " " & intYears & " Years " & intMonths &
" Months " & sngNumDays & " Days"
Me.Protect
Next
End Sub

Hi,
Using the "Datedif" function try:
=DATEDIF(A1,TODAY(),"md")


HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=489700

"Dean" wrote:

I need a way to subtract the difference between two dates and exprss the
difference in a format simular to 10 years 6 months 7 days. Assume a start
date of 12/19/1943 and an end date of Today(). I have no trouble using
datedif to get the number of years as 61 years, months as 11. I have now
idea of how to convert the total number of days between the two dates to the
correct number days. sngNumDays = DateDiff("d", DateOne, Now) gives the
total number of days (22657).

My current Code:

Private Sub cmdAge_Click()
Dim DateOne As Date
Dim intYears As Integer
Dim intMonths As Integer
Dim sngNumDays As Single
Dim introw As Integer
For introw = 2 To 4 '3 different dates are
entered on rows 2 through 4
DateOne = Cells(introw, 13).Value 'get the start date from
the spreadsheet
sngNumDays = DateDiff("d", DateOne, Now) 'total number of years
in the period
intYears = Int(sngNumDays / 365)
intMonths = DateDiff("m", DateOne, Now) 'months is total months
in the time period
intMonths = intMonths - (intYears * 12) 'convert to months
difference in the current year
sngNumDays = DateDiff("d", DateOne, Now) 'total number of days in
the period
Me.Unprotect
'print the results to the row and column on the spread sheet
Cells(introw, 14).Value = " " & intYears & " Years " & intMonths &
" Months " & sngNumDays & " Days"
Me.Protect
Next
End Sub

Hi Dean, You got lucky. I just read a solution to your problem on Chip
Pearsons website www.cpearson.com. I hope it helps. Apparently you can not do
it in VBA with the DateDiff function. Cheers, Ross.

Function Age(Date1 As Date, Date2 As Date) As String
Dim Y As Integer
Dim M As Integer
Dim D As Integer
Dim Temp1 As Date
Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Y = Year(Date2) - Year(Date1) + (Temp1 Date2)
M = Month(Date2) - Month(Date1) - (12 * (Temp1 Date2))
D = Day(Date2) - Day(Date1)
If D < 0 Then
M = M - 1
D = Day(DateSerial(Year(date2), Month(date2), 0)) + D
End If
Age = Y & " years " & M & " months " & D & " days"
End Function