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

#### Manish Kumawat

##### Member
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 <<<
Code:
``````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:

#### Manish Kumawat

##### Member
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

Code:
``````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

##### 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.