Financial Quarter from dd/mm/yyyy

[pablo bellissimo]

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

[Ron de Bruin]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Tom Ogilvy]

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" <[email protected]> wrote in
message news:[email protected]...
> 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

[pablo bellissimo]

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" <[email protected]> wrote in
> message news:[email protected]...
> > 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
>
>
>