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

zozew

Registered User.
Local time
Today, 23:53
Joined
Nov 18, 2010
Messages
199
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

Can anyone please help me keep some of my hair....

Much appreciated and
 

plog

Banishment Pending
Local time
Today, 10:53
Joined
May 11, 2011
Messages
11,611
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.
Local time
Today, 23:53
Joined
Nov 18, 2010
Messages
199
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
Local time
Today, 10:53
Joined
May 11, 2011
Messages
11,611
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.
Local time
Today, 23:53
Joined
Nov 18, 2010
Messages
199
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
Local time
Today, 10:53
Joined
May 11, 2011
Messages
11,611
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.
Local time
Today, 23:53
Joined
Nov 18, 2010
Messages
199
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.
Local time
Today, 23:53
Joined
Nov 18, 2010
Messages
199
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.
Local time
Today, 15:53
Joined
Feb 1, 2001
Messages
299
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

..forever waiting... waiting for jellybean!
Local time
Today, 23:53
Joined
May 7, 2009
Messages
19,169
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)
      Date1 = DateAdd("yyyy", lngDiffYears, Date1)
   End If

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

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

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

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

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

   If booCalcSeconds Then
      lngDiffSeconds = Abs(DateDiff("s", Date1, Date2))
      Date1 = DateAdd("s", lngDiffSeconds, Date1)
   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.
Local time
Today, 23:53
Joined
Nov 18, 2010
Messages
199
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)
      Date1 = DateAdd("yyyy", lngDiffYears, Date1)
   End If

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

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

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

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

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

   If booCalcSeconds Then
      lngDiffSeconds = Abs(DateDiff("s", Date1, Date2))
      Date1 = DateAdd("s", lngDiffSeconds, Date1)
   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
Local time
Today, 15:53
Joined
Feb 19, 2013
Messages
16,553
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.
Local time
Today, 23:53
Joined
Nov 18, 2010
Messages
199
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 😁
 

Users who are viewing this thread

Top Bottom