Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Financial Quarter from dd/mm/yyyy
Hi All,
I regularly need to derive a financial quarter from a date (dd/mm/yyyy) so I decided to create a function to do it for me but I'm really struggling! The problem is that the year runs from April to March NOT Jan to Dec. I have a column of dates and I want the function to return the quarter and year something like Date is 25/04/2005 Result should be Q1 05/06 (ie the first quarter in the financial year which spans 2005 and 2006) I have got the Q bit sorted but the yy/yy bit just wont play ball. For what its worth, heres what I have done (which doesn't work!!!) Public Function FinancialQuarter (Mydate) Dim lngQ As Long Dim lngY As Long If Month(MyDate) / 3 <= 1 Then lngQ = 4 Else lngQ = Month(MyDate) / 3 - 1 End If If lngQ = 4 Then lngY = Mid(Year(MyDate), 3, 2) - 1 & "/" & Mid(Year(MyDate), 3, 2) Else lngY = Mid(Year(MyDate), 3, 2) & "/" & Mid(Year(MyDate), 3, 2) + 1 End If FinancialQuarter = "Q" & lngQ & " " & lngY End Function I'm sure there is a simple way to do this but I am only fairly new to this. Any help greatly appreciated. Paul |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula bar shows mm/dd/yyyy. To show dd/mm/yyyy. How? | Excel Discussion (Misc queries) | |||
How do I convert a birthdate format from yyyy/mm/dd to mm/dd/yyyy | Excel Worksheet Functions | |||
how do I change date from mm/dd/yyyy to dd:mm:yyyy format in Excel | New Users to Excel | |||
change birthday display from mm/dd/yyyy to HIDE the yyyy? | Excel Worksheet Functions | |||
opening excel file - date format problem: DD/MM/YYYY vs MM/DD/YYYY | Excel Programming |