G
Guest
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
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