Results 1 to 6 of 6

DateDiff in VBA

This is a discussion on DateDiff in VBA within the Excel Questions forums, part of the Question Forums category; Hi, i am using the falowing function for date difference a=1/1/2011 b=1/10/2012 using the =Datedif(a,b,"md") excel function i get ==> ...

  1. #1
    Board Regular
    Join Date
    Nov 2009
    Posts
    152

    Default DateDiff in VBA

    Hi,

    i am using the falowing function for date difference

    a=1/1/2011
    b=1/10/2012

    using the =Datedif(a,b,"md") excel function i get ==> 9 but i want to use the same thing in VBA code

    Dim one, two, three
    one = Sheet1.Cells(1, 1).Value (1/1/2011)

    two = Sheet1.Cells(1, 2).Value (1/10/2012)

    three = DateDiff("d", one, two)

    three=374 ?

    i got 374 ?

    how to get 9 using VBA code ?

    please help me

    prakash

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    15,452

    Default Re: DateDiff in VBA

    I have not tested this extensively, but here is a function that you can use to duplicate the output that the Excel DATEDIF function does....

    Code:
    Function xlDATEDIF(ByVal StartDate As Date, ByVal EndDate As Date, Interval As String) As Variant
      Dim NumOfYears As Long, NumOfMonths As Long, NumOfWeeks As Long, NumOfDays As Long, DaysDiff As Long, ydDaysDiff As Long, TSerial1 As Double, TSerial2 As Double
      If StartDate > EndDate Then
        Err.Raise 5
        Exit Function
      End If
      If InStr(1, "Y M D", Interval, vbTextCompare) Then
        Select Case UCase(Interval)
          Case "Y": xlDATEDIF = DateDiff("yyyy", StartDate, EndDate)
          Case "M": xlDATEDIF = DateDiff("m", StartDate, EndDate)
          Case "D": xlDATEDIF = EndDate - StartDate
        End Select
      Else
        NumOfYears = DateDiff("yyyy", StartDate, EndDate)
        DaysDiff = EndDate - StartDate
        TSerial1 = TimeSerial(Hour(StartDate), Minute(StartDate), Second(StartDate))
        TSerial2 = TimeSerial(Hour(EndDate), Minute(EndDate), Second(EndDate))
        If 24 * (TSerial2 - TSerial1) < 0 Then EndDate = DateAdd("d", -1, EndDate)
        StartDate = DateSerial(Year(EndDate), Month(StartDate), Day(StartDate))
        If StartDate > EndDate Then
          StartDate = DateAdd("yyyy", -1, StartDate)
          NumOfYears = NumOfYears - 1
        End If
        ydDaysDiff = EndDate - StartDate
        NumOfMonths = DateDiff("m", StartDate, EndDate)
        StartDate = DateSerial(Year(EndDate), Month(EndDate), Day(StartDate))
        If StartDate > EndDate Then
          StartDate = DateAdd("m", -1, StartDate)
          NumOfMonths = NumOfMonths - 1
        End If
        NumOfDays = Abs(DateDiff("d", StartDate, EndDate))
        Select Case UCase(Interval)
          Case "YM": xlDATEDIF = NumOfMonths
          Case "YD": xlDATEDIF = ydDaysDiff
          Case "MD": xlDATEDIF = NumOfDays
          Case Else
        End Select
      End If
    End Function

  3. #3
    Board Regular
    Join Date
    Nov 2009
    Posts
    152

    Default Re: DateDiff in VBA

    hi

    thanks for reply.but it is not worked

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    15,452

    Default Re: DateDiff in VBA

    Quote Originally Posted by prakash_moturu View Post
    thanks for reply.but it is not worked
    Simply saying "it does not work" is not very useful... tell us what dates you tried, what interval you specified and what result you got... then we can test it out on our computers in order to see what is going on.

  5. #5
    GTO
    GTO is offline
    Board Regular
    Join Date
    Dec 2008
    Location
    Phoenix, Arizona
    Posts
    4,975

    Default Re: DateDiff in VBA

    Hi there,

    I did not think to put in error handling like Rick did, but was thinking evaluate might be one way.

    Sheet1
    ABC
    11/1/2011vba--->12
    21/13/2012wks function--->12
    Excel 2010

    Worksheet Formulas
    CellFormula
    C1=vbaDateDiff(A1,A2,"md")
    C2=DATEDIF(A1,A2,"md")



    Code:
    Option Explicit
        
    Sub test()
        MsgBox vbaDateDiff("1/1/2010", "12/12/2010", "m")
    End Sub
        
    Function vbaDateDiff(ByVal FirstDateCell As String, ByVal SecondDateCell As String, ByVal StringCode As String) As Long
        
        vbaDateDiff = Evaluate("DATEDIF(DATEVALUE(""" & FirstDateCell & """),DATEVALUE(""" & SecondDateCell & """),""" & StringCode & """)")
    End Function

    Hope that helps,

    Mark

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    15,452

    Default Re: DateDiff in VBA

    Quote Originally Posted by GTO View Post
    Hi there,

    I did not think to put in error handling like Rick did, but was thinking evaluate might be one way.
    Code:
    Option Explicit
     
    Sub test()
        MsgBox vbaDateDiff("1/1/2010", "12/12/2010", "m")
    End Sub
     
    Function vbaDateDiff(ByVal FirstDateCell As String, ByVal SecondDateCell As String, ByVal StringCode As String) As Long
     
        vbaDateDiff = Evaluate("DATEDIF(DATEVALUE(""" & FirstDateCell & """),DATEVALUE(""" & SecondDateCell & """),""" & StringCode & """)")
    End Function
    Personally, I would not recommend this solution because it ultimately uses the worksheet's DATEDIF function. The reason (from a previous posting of mine)...

    I know others disagree with me on this, but I would recommend not using DATEDIF, especially if the worksheet will be used for something important. Here is a post I have given in the past explaining why I am making this recommendation...

    You might want to reconsider using the DATEDIF function. It is an undocumented (and, thus, probably an unsupported) Excel function which appears to be broken in XL2007 at Service Pack 2. Someone recently posted this message as part of a newsgroup question...

    *********************************************************************
    =DATEDIF(DATE(2009,6,27),DATE(2012,1,5),"md")

    In XL2003, the above formula gives me the correct answer of 9. However,
    in Excel 2007, it gives me 122. The 122 increases in value until it
    hits 143 on 1/26/2012 and then, on 1/27/2012, the difference becomes 0.
    *********************************************************************

    An informal survey of fellow MVPs shows the above formula works correctly in the initial release of XL2007 and its SP1, but does not work correctly in SP2; hence, it appears to be broken at that level. The problem is that the extent of the breakage is unknown (and probably indeterminable). In addition, I would say, being an undocumented (and, thus, probably an unsupported) function, the odds of Microsoft spending the time to search down and fix whatever broke is slim. In addition, again because it is probably unsupported, the extent of any future breakage in the function due to other code change Microsoft makes elsewhere in Excel is unknowable... something that works today may not work tomorrow and Microsoft will probably never fix it. This would seem to mean that DATEDIF cannot be counted on to work correctly from XL2007 SP2 onward. And even if Microsoft did fix the problem in a subsequent Service Pack, any of your users who remained at SP2 would be subjected to incorrect result.

    ADDITIONAL FOLLOW-UP #1
    -------------------------------
    There is an indication that this might have been fixed in XL2010, however it remains broken in XL2007 as SP2, so if you are in a mixed environment of these two versions, you would be asking for trouble to continue using it. And even if you move wholly to XL2010, there is always the "they broke it once so what would stop them from breaking it again" possibility. Personally, I am still recommending that DATEDIF not be used, but the final decision as to how much you are willing to risk your data to this undocumented function is up to you.

    ADDITIONAL FOLLOW-UP #2
    -------------------------------
    A Microsofto Answers forum regular named joeu2004...

    http://answers.microsoft.com/en-us/p...0-f4608def4860

    posted this link...

    http://office.microsoft.com/en-us/he...001160981.aspx

    which documents DATEDIF for the "Microsoft Office SharePoint Server 2007" and "Windows SharePoint Services 3.0". Since the first one is an extension of Office, one might conclude that DATEDIF is "officially" documented. Personally, I don't conclude that, but to be fair I thought I would include the link and let you draw your own conclusion. I am still holding firm to my recommendation that DATEDIF not be used anymore (for the reasons I gave in the paragraph before my ADDITIONAL FOLLOW-UP #1.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com