424,490 Members | 900 Online
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
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" 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" 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 ableto find out which financial years the two dates cover, and populatecolunms in a database with how many months in each financial year ieif start date was 1/1/2004 to 1/1/2005 there would be 4 months in thefinancial 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.