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

Hi pablo

This add -in have a option to insert a column with a formula for you
http://www.rondebruin.nl/datarefiner.htm


EasyFilter can filter for you without inserting the formulas
http://www.rondebruin.nl/easyfilter.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


"pablo bellissimo" wrote in message
...
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

Public Function Qtr(sStr As String)
Dim dt As Date, dt1 As Date
Dim Yr As Long
dt = CDate(sStr)
dt1 = DateSerial(Year(dt), Month(dt) + 9, 1)
Yr = Format(dt1, "YY") - 1
Qtr = Format(dt1, "\QQ ") & Format(Yr, "00/") & Format(dt1, "YY")
End Function

Sub Tester1()
For i = 1 To 12
dt = DateSerial(2005, i, 25)
Debug.Print dt, Qtr(Format(dt, "mm/dd/yy"))
Next
End Sub

produces:
01/25/2005 Q4 04/05
02/25/2005 Q4 04/05
03/25/2005 Q4 04/05
04/25/2005 Q1 05/06
05/25/2005 Q1 05/06
06/25/2005 Q1 05/06
07/25/2005 Q2 05/06
08/25/2005 Q2 05/06
09/25/2005 Q2 05/06
10/25/2005 Q3 05/06
11/25/2005 Q3 05/06
12/25/2005 Q3 05/06

Might be able to cobble something together from that.

--
Regards,
Tom Ogilvy


"pablo bellissimo" wrote in
message ...
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

Absolute genius!! I'm not sure I completely understand it... but it works
perfectly!

Many Thanks

Paul

"Tom Ogilvy" wrote:

Public Function Qtr(sStr As String)
Dim dt As Date, dt1 As Date
Dim Yr As Long
dt = CDate(sStr)
dt1 = DateSerial(Year(dt), Month(dt) + 9, 1)
Yr = Format(dt1, "YY") - 1
Qtr = Format(dt1, "\QQ ") & Format(Yr, "00/") & Format(dt1, "YY")
End Function

Sub Tester1()
For i = 1 To 12
dt = DateSerial(2005, i, 25)
Debug.Print dt, Qtr(Format(dt, "mm/dd/yy"))
Next
End Sub

produces:
01/25/2005 Q4 04/05
02/25/2005 Q4 04/05
03/25/2005 Q4 04/05
04/25/2005 Q1 05/06
05/25/2005 Q1 05/06
06/25/2005 Q1 05/06
07/25/2005 Q2 05/06
08/25/2005 Q2 05/06
09/25/2005 Q2 05/06
10/25/2005 Q3 05/06
11/25/2005 Q3 05/06
12/25/2005 Q3 05/06

Might be able to cobble something together from that.

--
Regards,
Tom Ogilvy


"pablo bellissimo" wrote in
message ...
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