CODEWITHSUNDEEP
vbaexcel
Matt_DontKnow
Matt_DontKnow

Reputation: 49

Datedif function in VBA

I've got a quick question. Is there any solution to use DATEDIF function in VBA. It's a hidden function in Excel. I'm not asking about DATEDIFF function with two 'F' at the end. Thanks in advance.

Upvotes: 0

Views: 4925

Answers (3)

Harry S
Harry S

Reputation: 511

    Function DateDif(D1 As Date, D2 As Date, DDS$) As Integer
Dim DHi As Date, DLow As Date
Dim DifS$: DifS = "DATEDIF(" & Chr(34)
Dim dCom: dCom = Chr(34) & "," & Chr(34)
Dim DEnd: DEnd = Chr(34) & ")"
If D2 < D1 Then
    DateDif = Evaluate(DifS & D2 & dCom & D1 & dCom & DDS & DEnd)
Else
    DateDif = Evaluate(DifS & D1 & dCom & D2 & dCom & DDS & DEnd)
End If

End Function

' may be worth a play

Upvotes: 0

Vegard
Vegard

Reputation: 4917

This may not be exactly an answer, but seeing as it is too long for a comment and still somewhat relevant to your usage, I'll submit it.

Okey, for example we have two different dates. First one is "2016-02-14" and the second one is "2016-03-10". DateDif function will return 0, which is appropriate result but DateDiff will return 1 instead of 0. DATEDIF("2016-02-14"; "2016-03-10";"ym") and vba DATEDIFF("m","2016-02-14", "2016-03-10")

The reason DateDiff and DateDif gives you two different values in this usage seems to be because one of them calculates the full month difference and the other counts the offset of the months themselves without paying attention to the days.

DateDiff:
https://msdn.microsoft.com/en-us/library/b5xbyt6f(v=vs.90).aspx

Similarly, the return value for DateInterval.Month is calculated purely from the year and month parts of the arguments

DateDif:
https://msdn.microsoft.com/en-us/library/cc767548.aspx

"M" The number of complete months in the period.

I take from this that DateDiff returns the count of how far away the two months are from each other (February and March are always 1 month apart, because it doesn't care what day of the month you supplied) while DateDif seems to count the number of days to determine whether a "complete" month has been achieved or not.

Upvotes: 0

Porcupine911
Porcupine911

Reputation: 928

You can use the Evaluate method...

Evaluate("DATEDIF(A1,A2,""Y"")")

And yes, you can use variables...

Sub TestEvaluateWithVariables()

    Dim strVariable As String
    Dim dateStartDate As Date
    Dim dateEndDate As Date

    dateStartDate = #9/1/2002#
    dateEndDate = #11/30/2003#

    strVariable = "DATEDIF(""" & dateStartDate & """,""" & dateEndDate & """,""YD"")"

    Cells(3, 3) = Evaluate(strVariable)

End Sub

Upvotes: 1

Related Questions