• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

UDF for calculating the date (Year,Month and Day)

Dear Sir,

I have created a user defined function which calculates the year, month and day.
the formula which I have used is:

=CONCATENATE(DATEDIF(A1,A2,"y")," Yr ",DATEDIF(A1,A2,"ym")," M ",DATEDIF(A1,A2,"md")," D")

but the UDF is not providing the same answer as per the formula. Please help me to understand the same.

>>> use code - tags <<<
Option Explicit
Function mdate(x As Variant, y As Variant)
Dim Fyear As Integer
Dim Fmonth As Integer
Dim FDay As Integer
Fyear = DateDiff("yyyy", x, y)
Fmonth = (DateDiff("M", x, y) / 12 - DateDiff("yyyy", x, y)) * 12
FDay = DateDiff("d", x, y)
mdate = Application.WorksheetFunction.Concat("", Fyear, " Yr, ", Fmonth, " M, ", FDay, " D")
End Function
Last edited by a moderator:
Please refer to my earlier query in which I have asked for the UDF. I have gone through this function, but this function is also not giving the exact answer to my query. Please help me


Function MDATE(x As Date, y As Date)

Dim Days As Integer
Dim Months As Integer
Dim Years As Integer
Dim ty As Integer
Dim tt As Date

Days = 0
Months = 0
Years = 0
tt = x

ty = DateDiff("d", x, y) - 1

For i = 0 To ty

Days = Days + 1
tt = tt + 1

If Day(x) = Day(tt) Then

Days = 0
Months = Months + 1
End If

If Months = 12 Then

Days = 0
Months = 0
Years = Years + 1

End If

Next i

MDATE = Years & " Years, " & Months & " Months, " & Days & " Days"

End Function


Excel Ninja
Manish Kumawat
Refer As You've noticed Formula and VBA DateDiff works different way, that's why there will be differences.
Your not giving the exact answer to my query.
Question: What is Your the exact answer to You query?
hmm? ...There can be four different last days for months.