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

[Manish Kumawat]

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

 

[Manish Kumawat]

Hi,
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

Regards,
Manish

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

 

[vletm]

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.