Change Fiscal Year Start Date

[Sue]

When using the DatePart "q" for quarter do I change the
start of the quarter to be October 1. I found something
in help about using a FirstDayOfYear but don't know how or
where to set this. I have regular date fields and want to
create a field that lists the correct fiscal quarter
according to my Oct 1 start. I export the data to Excel
and do charts and pivot tables. Access and Excel both
understand Jan as the start of the year. Help is
appreciated.

 

[MGFoster]

When using the DatePart "q" for quarter do I change the
start of the quarter to be October 1. I found something
in help about using a FirstDayOfYear but don't know how or
where to set this. I have regular date fields and want to
create a field that lists the correct fiscal quarter
according to my Oct 1 start. I export the data to Excel
and do charts and pivot tables. Access and Excel both
understand Jan as the start of the year. Help is
appreciated.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

There's a FirstDayOfWeek and a FirstWeekOfYear, but I don't believe
their is a FirstDayOfYear.

I usually use something like this (debug window):

? datepart("q",dateadd("m",3,#10/1/2005#))
1

Use the offset of 3 months on the current, or defined, date. As you can
see above, if the defined date is 1 Oct 2005, then the whole expression
will return 1st Qtr; 1 Jan 2006 will return 2nd Qtr; etc.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQiOiJYechKqOuFEgEQLpIACg70TnQ556tS6z4yGHGcXdKveh9kYAn15y
GDDmHz/A1sCHrfOUihfv0R4d
=qIma
-----END PGP SIGNATURE-----

 

[Sue]

This does not fix the problem. What it does give me is a
field with 1, 2, 3,or 4 for the quarter starting in
October but it does not help with the years. Example,
10/1/2004 is Qtr 1 2005. So I end up with reports that
don't understand what 1 belongs to. Somehow I have to
redefine FirstWeekOfYear. Can anyone help with that?
Thre is little to nothing on how to define it. Thanks.
Sue

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Change the interval indicator in the DatePart() function to "yyyy" to
get the FY of the date:

? datepart("yyyy",dateadd("m",3,#10/1/2005#))
2006

Come to think of it, you could just make a function for the whole thing:

Public Function FYDatePart(strIntervalType As String, _
dteDate As Date) As Integer
' Purpose:
' Returns the date part of the indicated date.
' In:
' strIntervalType One of the interval setting types described
' in the DatePart Function Help article.
' dteDate The indicated date.
' Out:
' Integer The numeric date part
'

' Change the interval value in the DateAdd to conform to your FY:
' If your FY starts 3 months before Jan 1 use 3
' If your FY starts 6 months before Jan 1 use 6
' etc.

FYDatePart = DatePart(strIntervalType, DateAdd("m", 3, dteDate)

End Function

Call it like this:

' To find the FY quarter of 25 Feb 2005 (2nd qtr)
Dim intQtr As Integer
intQtr = FYDatePart("q", #2/25/05#)

' To find the FY of 25 Feb 2005 (2005)
Dim intYear As Integer
intYear = FYDatePart("yyyy", #2/25/05#)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQiS554echKqOuFEgEQJHkACeJN4vRF2CymEMHxC4IEn7G3XV7q4AoPJp
WNujSOQQ652F4ALjrs5XRYB5
=X5bY
-----END PGP SIGNATURE-----