By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 900 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Finacial years

P: n/a
Hi

If i have a field in a database start date and end date, how am I able
to find out which financial years the two dates cover, and populate
colunms in a database with how many months in each financial year ie
if start date was 1/1/2004 to 1/1/2005 there would be 4 months in the
financial year 03/04 and 8 months in the financial year 04/05
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
See if this helps --

From my files - see below my SIG
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

Get The Fiscal Year Or Fiscal Month Of A Particular Date By Using An Expression

To get the fiscal year or fiscal month for a OrderDate where the fiscal year
begins June 16 use the following expressions:

Fyear = Year([OrderDate])-IIf([OrderDate]<
DateSerial(Year([OrderDate]),6,16),1,0)

Fmonth = (Month([OrderDate])+IIf(Day([OrderDate])<16,6,7)-1) Mod 12+1 NOTE: The
FYear and FMonth expressions can be used for for any beginning date of a Fiscal
year by modifying the Date field, the day and the month, to the appropriate
values for the fiscal year. For example, if the fiscal year begins on 9/15 of
the current calendar year, you can modify the previous expressions as follows:

Fyear = Year([FieldName])-IIf([FieldName]<
DateSerial(Year([FieldName]),9,15),1,0)

FMonth: (Month([FieldName])+IIf(Day([FieldName])<15,9,10)-1) Mod 12+1
If the fiscal year begins on 9/15 of the previous calendar year, you can modify
the FYear expression as follows:

Fyear = Year([FieldName])-IIf([FieldName

"Mr C" <ma*********@hotmail.com> wrote in message
news:70*************************@posting.google.co m...
Hi

If i have a field in a database start date and end date, how am I able
to find out which financial years the two dates cover, and populate
colunms in a database with how many months in each financial year ie
if start date was 1/1/2004 to 1/1/2005 there would be 4 months in the
financial year 03/04 and 8 months in the financial year 04/05

Nov 12 '05 #2

P: n/a
I was able to get a working scenario together with the DateDiff()
function. However, you'll need to determine how your records will
handle projects longer than one year that span 3 fiscal years, for
instance. It's do-able, but requires some additional planning.

ma*********@hotmail.com (Mr C) wrote in message news:<70*************************@posting.google.c om>...
Hi

If i have a field in a database start date and end date, how am I able
to find out which financial years the two dates cover, and populate
colunms in a database with how many months in each financial year ie
if start date was 1/1/2004 to 1/1/2005 there would be 4 months in the
financial year 03/04 and 8 months in the financial year 04/05

Nov 12 '05 #3

P: n/a
The way I have tackled these situations is as follows:

In one field of my table, I would create a field as:
DateSerial(Year(Date()),Month(Date())+1,1)-1

This will set the date to the last day of the month. For this example,
I am calling this field "Period". If course you can have this update by an
update query or the example above uses today's date.
I would create another field Year. Use an update query as follows:
IIf(Month([Period])>8,Year([Period])+1,Year([Period])). If 8 (August) is
the last month of the fiscal year. Otherwise, change the formula to suite
your needs.
Hope this helps.

--
Dean Covey
www.coveyaccounting.com

MS-Office Certified:
http://www.microsoft.com/learning/mc...st/default.asp

"Mr C" <ma*********@hotmail.com> wrote in message
news:70*************************@posting.google.co m...
Hi

If i have a field in a database start date and end date, how am I able
to find out which financial years the two dates cover, and populate
colunms in a database with how many months in each financial year ie
if start date was 1/1/2004 to 1/1/2005 there would be 4 months in the
financial year 03/04 and 8 months in the financial year 04/05

Nov 12 '05 #4

P: n/a
dOn 8 Apr 2004 02:53:27 -0700, ma*********@hotmail.com (Mr C) wrote:
If i have a field in a database start date and end date, how am I able
to find out which financial years the two dates cover, and populate
colunms in a database with how many months in each financial year ie
if start date was 1/1/2004 to 1/1/2005 there would be 4 months in the
financial year 03/04 and 8 months in the financial year 04/05


Use a table.

--
Mike Sherrill
Information Management Systems
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.