# From number of days to Years, months and days DATEDIFF madness!! (1 Viewer)

#### zozew

##### Registered User.
Hi Everybody,

I have this great little sheet doing some calculations with VBA and excel formulas. All is well until I decided to convert the resulting days into yers months and days..

=DATEDIF(0,3675,"y") & " years, " & DATEDIF(0,3675,"ym")&" months, " & DATEDIF(0,3675,"md")&" days"

And the result is below...all look great right....NO!!!! its supposed to be 25 days not 22!!!
 10 years, 0 months, 22 days
(the 3675 above comes from a cell D35)

I've checked with various calculators on google and the resulting days should be 25

Much appreciated and

#### plog

##### Banishment Pending
Even Microsoft doesn't recommend relying on "md":

Walking through your example, 22 makes sense. When you convert 0 to a date (Right click, format Cells, Date) it becomes 1/0/1900, When you convert 3675 to a date it becomes 1/22/1910. When you use DateDif with "md" it simply subtracts the day values from one another (22-0 = 22).

Why are you using 0 as a date? And what happened in 1910?

#### zozew

##### Registered User.
Hi Plog,

Well i actually dont care about 1910. An the solution I used found in several places...

I just have a number of days that I want to be covered into y, m and d...so I guess ill have to divide 365 with 12, use the 30.41 result, and then the remaining number convert that into days rounded up...then it kinda works

#### plog

##### Banishment Pending
You're keeping us behind an opaque window. What are you actually trying to accomplish? Perhaps there is a better method we could help with if we knew the actual aim.

#### zozew

##### Registered User.
plog my mistake,

I just want to print out the number I have in days displayed as years months and days with no connection to real date. below I tried something...

My simple solution or kinda solution...

I have to divide 365 with 12, use the 30.416 result, and then the remaining number convert that into days rounded up...then it kinda works

so..

3675 days / 30.416 = 120.8246 months

0.8246 x 30.416 = 25.081 so 25 days

10years, 0 months, 25 days

#### plog

##### Banishment Pending
Kinda works is the best you can hope for when working with years, months and days.

Months=28,29,30 &31 days
Years = 365 & 366 days

#### zozew

##### Registered User.
now im trying to get the years months and days separated..hmmm any suggestion how to do that..

years is pretty simple I can round that to whole numbers but months/days is trickier...

#### zozew

##### Registered User.
Kinda works is the best you can hope for when working with years, months and days.

Months=28,29,30 &31 days
Years = 365 & 366 days
.....help....

3675 days / 30.416 = 120.8246 months

that's 10 full years and 0 months and 0.8246 days

then to get the 0.8246 x 30.416 is 25.08 rounded down to 25 days

my brain is starting to melt slightly...how do I get the months and then all into a formula....?

#### Darrell

##### Registered User.
I
=DATEDIF(0,3675,"y") & " years, " & DATEDIF(0,3675,"ym")&" months, " & DATEDIF(0,3675,"md")&" days"

And the result is below...all look great right....NO!!!! its supposed to be 25 days not 22!!!
 10 years, 0 months, 22 days

I think you neglected to take Leap Years into account - not helped by the fact that 1900 wasn't a leap year but MS Excel incorrectly states that it was.

#### arnelgp

use custom function?
Code:
``````'***************** Code Start **************
Public Function Diff2Dates(interval As String, Date1 As Variant, Date2 As Variant, _
Optional ShowZero As Boolean = False) As Variant
'Author:    ? Copyright 2001 Pacific Database Pty Limited
'           Graham R Seach MCP MVP gseach@pacificdb.com.au
'           Phone: +61 2 9872 9594  Fax: +61 2 9872 9593
'           This code is freeware. Enjoy...
'           (*) Amendments suggested by Douglas J. Steele MVP
'
'Description:   This function calculates the number of years,
'               months, days, hours, minutes and seconds between
'               two dates, as elapsed time.
'
'Inputs:    Interval:   Intervals to be displayed (a string)
'           Date1:      The lower date (see below)
'           Date2:      The higher date (see below)
'           ShowZero:   Boolean to select showing zero elements
'
'Outputs:   On error: Null
'           On no error: Variant containing the number of years,
'               months, days, hours, minutes & seconds between
'               the two dates, depending on the display interval
'               selected.
'           If Date1 is greater than Date2, the result will
'               be a negative value.
'           The function compensates for the lack of any intervals
'               not listed. For example, if Interval lists "m", but
'               not "y", the function adds the value of the year
'               component to the month component.
'           If ShowZero is True, and an output element is zero, it
'               is displayed. However, if ShowZero is False or
'               omitted, no zero-value elements are displayed.
'               For example, with ShowZero = False, Interval = "ym",
'               elements = 0 & 1 respectively, the output string
'               will be "1 month" - not "0 years 1 month".

On Error GoTo Err_Diff2Dates

Dim booCalcYears As Boolean
Dim booCalcMonths As Boolean
Dim booCalcDays As Boolean
Dim booCalcHours As Boolean
Dim booCalcMinutes As Boolean
Dim booCalcSeconds As Boolean
Dim booCalcWeeks As Boolean
Dim booSwapped As Boolean
Dim dtTemp As Date
Dim intCounter As Integer
Dim lngDiffYears As Long
Dim lngDiffMonths As Long
Dim lngDiffDays As Long
Dim lngDiffHours As Long
Dim lngDiffMinutes As Long
Dim lngDiffSeconds As Long
Dim lngDiffWeeks As Long
Dim varTemp As Variant

Const INTERVALS As String = "dmyhnsw"

'Check that Interval contains only valid characters
interval = LCase\$(interval)
For intCounter = 1 To Len(interval)
If InStr("/: ", Mid(interval, intCounter, 1)) > 0 Then
'ignore
Else
If InStr(1, INTERVALS, Mid\$(interval, intCounter, 1)) = 0 Then
Exit Function
End If
End If
Next intCounter

'Check that valid dates have been entered
If IsNull(Date1) Then Exit Function
If IsNull(Date2) Then Exit Function
If Not (IsDate(Date1)) Then Exit Function
If Not (IsDate(Date2)) Then Exit Function

'If necessary, swap the dates, to ensure that
'Date1 is lower than Date2
If Date1 > Date2 Then
dtTemp = Date1
Date1 = Date2
Date2 = dtTemp
booSwapped = True
End If

Diff2Dates = Null
varTemp = Null

'What intervals are supplied
booCalcYears = (InStr(1, interval, "y") > 0)
booCalcMonths = (InStr(1, interval, "m") > 0)
booCalcDays = (InStr(1, interval, "d") > 0)
booCalcHours = (InStr(1, interval, "h") > 0)
booCalcMinutes = (InStr(1, interval, "n") > 0)
booCalcSeconds = (InStr(1, interval, "s") > 0)
booCalcWeeks = (InStr(1, interval, "w") > 0)

'Get the cumulative differences
If booCalcYears Then
lngDiffYears = Abs(DateDiff("yyyy", Date1, Date2)) - _
IIf(Format\$(Date1, "mmddhhnnss") <= Format\$(Date2, "mmddhhnnss"), 0, 1)
End If

If booCalcMonths Then
lngDiffMonths = Abs(DateDiff("m", Date1, Date2)) - _
IIf(Format\$(Date1, "ddhhnnss") <= Format\$(Date2, "ddhhnnss"), 0, 1)
End If

If booCalcWeeks Then
lngDiffWeeks = Abs(DateDiff("w", Date1, Date2)) - _
IIf(Format\$(Date1, "hhnnss") <= Format\$(Date2, "hhnnss"), 0, 1)
End If

If booCalcDays Then
lngDiffDays = Abs(DateDiff("d", Date1, Date2)) - _
IIf(Format\$(Date1, "hhnnss") <= Format\$(Date2, "hhnnss"), 0, 1)
End If

If booCalcHours Then
lngDiffHours = Abs(DateDiff("h", Date1, Date2)) - _
IIf(Format\$(Date1, "nnss") <= Format\$(Date2, "nnss"), 0, 1)
End If

If booCalcMinutes Then
lngDiffMinutes = Abs(DateDiff("n", Date1, Date2)) - _
IIf(Format\$(Date1, "ss") <= Format\$(Date2, "ss"), 0, 1)
End If

If booCalcSeconds Then
lngDiffSeconds = Abs(DateDiff("s", Date1, Date2))
End If

If booCalcYears And (lngDiffYears > 0 Or ShowZero) Then
varTemp = lngDiffYears & IIf(lngDiffYears <> 1, " years", " year")
End If

If booCalcMonths And (lngDiffMonths > 0 Or ShowZero) Then
If booCalcMonths Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffMonths & IIf(lngDiffMonths <> 1, " months", " month")
End If
End If

If booCalcWeeks And (lngDiffWeeks > 0 Or ShowZero) Then
If booCalcWeeks Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffWeeks & IIf(lngDiffWeeks <> 1, " weeks", " week")
End If
End If

If booCalcDays And (lngDiffDays > 0 Or ShowZero) Then
If booCalcDays Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffDays & IIf(lngDiffDays <> 1, " days", " day")
End If
End If

If booCalcHours And (lngDiffHours > 0 Or ShowZero) Then
If booCalcHours Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffHours & IIf(lngDiffHours <> 1, " hours", " hour")
End If
End If

If booCalcMinutes And (lngDiffMinutes > 0 Or ShowZero) Then
If booCalcMinutes Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffMinutes & IIf(lngDiffMinutes <> 1, " minutes", " minute")
End If
End If

If booCalcSeconds And (lngDiffSeconds > 0 Or ShowZero) Then
If booCalcSeconds Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffSeconds & IIf(lngDiffSeconds <> 1, " seconds", " second")
End If
End If

If booSwapped Then
varTemp = "-" & varTemp
End If

Diff2Dates = Trim\$(varTemp)

End_Diff2Dates:
Exit Function

Err_Diff2Dates:
Resume End_Diff2Dates

End Function
'************** Code End *****************``````

#### zozew

##### Registered User.
use custom function?
Code:
``````'***************** Code Start **************
Public Function Diff2Dates(interval As String, Date1 As Variant, Date2 As Variant, _
Optional ShowZero As Boolean = False) As Variant
'Author:    ? Copyright 2001 Pacific Database Pty Limited
'           Graham R Seach MCP MVP gseach@pacificdb.com.au
'           Phone: +61 2 9872 9594  Fax: +61 2 9872 9593
'           This code is freeware. Enjoy...
'           (*) Amendments suggested by Douglas J. Steele MVP
'
'Description:   This function calculates the number of years,
'               months, days, hours, minutes and seconds between
'               two dates, as elapsed time.
'
'Inputs:    Interval:   Intervals to be displayed (a string)
'           Date1:      The lower date (see below)
'           Date2:      The higher date (see below)
'           ShowZero:   Boolean to select showing zero elements
'
'Outputs:   On error: Null
'           On no error: Variant containing the number of years,
'               months, days, hours, minutes & seconds between
'               the two dates, depending on the display interval
'               selected.
'           If Date1 is greater than Date2, the result will
'               be a negative value.
'           The function compensates for the lack of any intervals
'               not listed. For example, if Interval lists "m", but
'               not "y", the function adds the value of the year
'               component to the month component.
'           If ShowZero is True, and an output element is zero, it
'               is displayed. However, if ShowZero is False or
'               omitted, no zero-value elements are displayed.
'               For example, with ShowZero = False, Interval = "ym",
'               elements = 0 & 1 respectively, the output string
'               will be "1 month" - not "0 years 1 month".

On Error GoTo Err_Diff2Dates

Dim booCalcYears As Boolean
Dim booCalcMonths As Boolean
Dim booCalcDays As Boolean
Dim booCalcHours As Boolean
Dim booCalcMinutes As Boolean
Dim booCalcSeconds As Boolean
Dim booCalcWeeks As Boolean
Dim booSwapped As Boolean
Dim dtTemp As Date
Dim intCounter As Integer
Dim lngDiffYears As Long
Dim lngDiffMonths As Long
Dim lngDiffDays As Long
Dim lngDiffHours As Long
Dim lngDiffMinutes As Long
Dim lngDiffSeconds As Long
Dim lngDiffWeeks As Long
Dim varTemp As Variant

Const INTERVALS As String = "dmyhnsw"

'Check that Interval contains only valid characters
interval = LCase\$(interval)
For intCounter = 1 To Len(interval)
If InStr("/: ", Mid(interval, intCounter, 1)) > 0 Then
'ignore
Else
If InStr(1, INTERVALS, Mid\$(interval, intCounter, 1)) = 0 Then
Exit Function
End If
End If
Next intCounter

'Check that valid dates have been entered
If IsNull(Date1) Then Exit Function
If IsNull(Date2) Then Exit Function
If Not (IsDate(Date1)) Then Exit Function
If Not (IsDate(Date2)) Then Exit Function

'If necessary, swap the dates, to ensure that
'Date1 is lower than Date2
If Date1 > Date2 Then
dtTemp = Date1
Date1 = Date2
Date2 = dtTemp
booSwapped = True
End If

Diff2Dates = Null
varTemp = Null

'What intervals are supplied
booCalcYears = (InStr(1, interval, "y") > 0)
booCalcMonths = (InStr(1, interval, "m") > 0)
booCalcDays = (InStr(1, interval, "d") > 0)
booCalcHours = (InStr(1, interval, "h") > 0)
booCalcMinutes = (InStr(1, interval, "n") > 0)
booCalcSeconds = (InStr(1, interval, "s") > 0)
booCalcWeeks = (InStr(1, interval, "w") > 0)

'Get the cumulative differences
If booCalcYears Then
lngDiffYears = Abs(DateDiff("yyyy", Date1, Date2)) - _
IIf(Format\$(Date1, "mmddhhnnss") <= Format\$(Date2, "mmddhhnnss"), 0, 1)
End If

If booCalcMonths Then
lngDiffMonths = Abs(DateDiff("m", Date1, Date2)) - _
IIf(Format\$(Date1, "ddhhnnss") <= Format\$(Date2, "ddhhnnss"), 0, 1)
End If

If booCalcWeeks Then
lngDiffWeeks = Abs(DateDiff("w", Date1, Date2)) - _
IIf(Format\$(Date1, "hhnnss") <= Format\$(Date2, "hhnnss"), 0, 1)
End If

If booCalcDays Then
lngDiffDays = Abs(DateDiff("d", Date1, Date2)) - _
IIf(Format\$(Date1, "hhnnss") <= Format\$(Date2, "hhnnss"), 0, 1)
End If

If booCalcHours Then
lngDiffHours = Abs(DateDiff("h", Date1, Date2)) - _
IIf(Format\$(Date1, "nnss") <= Format\$(Date2, "nnss"), 0, 1)
End If

If booCalcMinutes Then
lngDiffMinutes = Abs(DateDiff("n", Date1, Date2)) - _
IIf(Format\$(Date1, "ss") <= Format\$(Date2, "ss"), 0, 1)
End If

If booCalcSeconds Then
lngDiffSeconds = Abs(DateDiff("s", Date1, Date2))
End If

If booCalcYears And (lngDiffYears > 0 Or ShowZero) Then
varTemp = lngDiffYears & IIf(lngDiffYears <> 1, " years", " year")
End If

If booCalcMonths And (lngDiffMonths > 0 Or ShowZero) Then
If booCalcMonths Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffMonths & IIf(lngDiffMonths <> 1, " months", " month")
End If
End If

If booCalcWeeks And (lngDiffWeeks > 0 Or ShowZero) Then
If booCalcWeeks Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffWeeks & IIf(lngDiffWeeks <> 1, " weeks", " week")
End If
End If

If booCalcDays And (lngDiffDays > 0 Or ShowZero) Then
If booCalcDays Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffDays & IIf(lngDiffDays <> 1, " days", " day")
End If
End If

If booCalcHours And (lngDiffHours > 0 Or ShowZero) Then
If booCalcHours Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffHours & IIf(lngDiffHours <> 1, " hours", " hour")
End If
End If

If booCalcMinutes And (lngDiffMinutes > 0 Or ShowZero) Then
If booCalcMinutes Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffMinutes & IIf(lngDiffMinutes <> 1, " minutes", " minute")
End If
End If

If booCalcSeconds And (lngDiffSeconds > 0 Or ShowZero) Then
If booCalcSeconds Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffSeconds & IIf(lngDiffSeconds <> 1, " seconds", " second")
End If
End If

If booSwapped Then
varTemp = "-" & varTemp
End If

Diff2Dates = Trim\$(varTemp)

End_Diff2Dates:
Exit Function

Err_Diff2Dates:
Resume End_Diff2Dates

End Function
'************** Code End *****************``````
thanks but i think this is a "little" overkill I'm going with this..

A1 = 3775

Year =INT(A1/365) = 10
Month = =INT(MOD(A1,365)/30) = 4
Days =MOD(MOD(A1,365),30) = 5

test: 10*365+4*30+5 = 3775

#### CJ_London

##### Super Moderator
Staff member
where does the 3675 value come from? - presumably the difference between 2 dates? In which case why not use them.

with just a number of days, you will only ever have an approximation as to what the numbers should be. Another basis would give a different answer of 10 years 5 months 2 days

#### zozew

##### Registered User.
where does the 3675 value come from? - presumably the difference between 2 dates? In which case why not use them.

with just a number of days, you will only ever have an approximation as to what the numbers should be. Another basis would give a different answer of 10 years 5 months 2 days
I understand your point, but the 3675 is actually not days between dates but rather accumulated "extra days" according to a few functions between two dates.

The two dates are used for elapsed time just as you suggest, but the accumulated extra days are not anchored to any real date. So.. To convert them into some resemblance of easier viewed time... I think the solution above works ok

Replies
3
Views
227
Replies
7
Views
147
Replies
7
Views
174
Replies
19
Views
356
Replies
7
Views
2,553