Hi All.
I have written a simple UDF that converts calendar date to my company's Fiscal date. Fiscal date = calendar date - 3 months. I could use Edate(A1,-3) but then I would need to format the cell.
I would like the UDF to do the conversion return the date and then set the format. It does not seem like I can format the select cell in the UDF?
Public Function Convert_Period_to_Date(thisdate As Date) As Date Dim new_month As Integer Dim new_year As Integer Select Case Month(thisdate) Case 4 To 12 new_month = Month(thisdate) - 3 new_year = Year(thisdate) Case 1 new_year = Year(thisdate) - 1 new_month = 10 Case 2 new_year = Year(thisdate) - 1 new_month = 11 Case 3 new_year = Year(thisdate) - 1 new_month = 12 End Select Convert_Period_to_Date = CDate(new_month & "/1/" & new_year) Cells(Selection.Row, Selection.Column).NumberFormat = "mmm-yy" End Function
Bookmarks