0

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.

Improve this question
9
  • Why would you want to use DateDif instead of DateDiff? They do the same thing... Commented Feb 14, 2016 at 21:30
  • 1
    Aren't they? If they was, I wouldn't ask question like this one :( Commented Feb 14, 2016 at 21:43
  • Yes, they are. The syntax is different, but they do the same thing. So again, why do you want to use DateDif instead of DateDiff? Commented Feb 14, 2016 at 21:55
  • I want to use it, to count difference between two dates in days. If between two days are 2 months which means 60 or 61 days in my case, I have to increment certain value. Commented Feb 14, 2016 at 21:56
  • 2
    @Matt_DontKnow To count the difference between two dates in days, merely subtract one from the other. Commented Feb 14, 2016 at 21:58

3 Answers 3

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Bingo. Thanks a lot.
Can i use it with variables?
Matt -- if this answer addresses your issue, you should mark it as correct so that future people with a similar question will identify this as the solution
I forgot about it. I've already marked it as correct answer. Thank you all.
0

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.

Improve this answer

Comments

0 
    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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.