=IF((ROUNDUP(MONTH(A1)/3,0)=1),"31/03/"&(YEAR(A1)),
IF((ROUNDUP(MONTH(A1)/3,0)=2),"30/06/"&(YEAR(A1)),
IF((ROUNDUP(MONTH(A1)/3,0)=3),"30/09/"&(YEAR(A1)),"31/12/"&(YEAR(A1)))))

I have the formulae that I want to convert to UDF and use it in my VBA
project.
Please let me know how do I write a function out of it.

A literal translation:

Function dd(ByVal dte As Date) As Date
Dim rDate As Date
If ((Application.RoundUp(Month(dte) / 3, 0) = 1)) Then
rDate = "31/03/" & (Year(dte))
Else
If (Application.RoundUp(Month(dte) / 3, 0) = 2) Then
rDate = "30/06/" & (Year(dte))
Else
If (Application.RoundUp(Month(dte) / 3, 0) = 3) Then
rDate = "30/09/" & (Year(dte))
Else
rDate = "31/12/" & (Year(dte))
End If
End If
End If
dd = rDate
End Function

OR

Function ddx(ByVal dte As Date) As Date
edays = Array(31, 30, 30, 31)
n = Int((Month(dte) - 1) / 3)
ddx = Str(edays(n)) & "/" & Str((n + 1) * 3) & "/" & Year(dte)
End Function

HTH

"shantanu oak" wrote:

=IF((ROUNDUP(MONTH(A1)/3,0)=1),"31/03/"&(YEAR(A1)),
IF((ROUNDUP(MONTH(A1)/3,0)=2),"30/06/"&(YEAR(A1)),
IF((ROUNDUP(MONTH(A1)/3,0)=3),"30/09/"&(YEAR(A1)),"31/12/"&(YEAR(A1)))))

I have the formulae that I want to convert to UDF and use it in my VBA
project.
Please let me know how do I write a function out of it.

Thanks.
But the resulting figure is in numbers. How do I format it as a date?

Shantanu


Toppers wrote:
A literal translation:

Function dd(ByVal dte As Date) As Date
Dim rDate As Date
If ((Application.RoundUp(Month(dte) / 3, 0) = 1)) Then
rDate = "31/03/" & (Year(dte))
Else
If (Application.RoundUp(Month(dte) / 3, 0) = 2) Then
rDate = "30/06/" & (Year(dte))
Else
If (Application.RoundUp(Month(dte) / 3, 0) = 3) Then
rDate = "30/09/" & (Year(dte))
Else
rDate = "31/12/" & (Year(dte))
End If
End If
End If
dd = rDate
End Function

OR

Function ddx(ByVal dte As Date) As Date
edays = Array(31, 30, 30, 31)
n = Int((Month(dte) - 1) / 3)
ddx = Str(edays(n)) & "/" & Str((n + 1) * 3) & "/" & Year(dte)
End Function

HTH

"shantanu oak" wrote:

=IF((ROUNDUP(MONTH(A1)/3,0)=1),"31/03/"&(YEAR(A1)),
IF((ROUNDUP(MONTH(A1)/3,0)=2),"30/06/"&(YEAR(A1)),
IF((ROUNDUP(MONTH(A1)/3,0)=3),"30/09/"&(YEAR(A1)),"31/12/"&(YEAR(A1)))))

I have the formulae that I want to convert to UDF and use it in my VBA
project.
Please let me know how do I write a function out of it.

Format the cell as a date manually (format|cell|number tab)

shantanu oak wrote:

Thanks.
But the resulting figure is in numbers. How do I format it as a date?

Shantanu

Toppers wrote:
A literal translation:

Function dd(ByVal dte As Date) As Date
Dim rDate As Date
If ((Application.RoundUp(Month(dte) / 3, 0) = 1)) Then
rDate = "31/03/" & (Year(dte))
Else
If (Application.RoundUp(Month(dte) / 3, 0) = 2) Then
rDate = "30/06/" & (Year(dte))
Else
If (Application.RoundUp(Month(dte) / 3, 0) = 3) Then
rDate = "30/09/" & (Year(dte))
Else
rDate = "31/12/" & (Year(dte))
End If
End If
End If
dd = rDate
End Function

OR

Function ddx(ByVal dte As Date) As Date
edays = Array(31, 30, 30, 31)
n = Int((Month(dte) - 1) / 3)
ddx = Str(edays(n)) & "/" & Str((n + 1) * 3) & "/" & Year(dte)
End Function

HTH

"shantanu oak" wrote:

=IF((ROUNDUP(MONTH(A1)/3,0)=1),"31/03/"&(YEAR(A1)),
IF((ROUNDUP(MONTH(A1)/3,0)=2),"30/06/"&(YEAR(A1)),
IF((ROUNDUP(MONTH(A1)/3,0)=3),"30/09/"&(YEAR(A1)),"31/12/"&(YEAR(A1)))))

I have the formulae that I want to convert to UDF and use it in my VBA
project.
Please let me know how do I write a function out of it.



--

Dave Peterson