Multiple dates into financial years.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to calculate the financial years from a table with over 10 years worth
of data for comparison, cannot use IIF as too many variables, anybody got a
funky shorthand way of doing this?
 
When does your fiscal year start? October? April? July?
If it is October the add 3 months to the date and format as year.
"FY " & Format(DateAdd("m",3,[YourDateField]),"yyyy")
April wouls subtract 3 months.
"FY " & Format(DateAdd("m",-3,[YourDateField]),"yyyy")
 
Cheers, that worked a treat and then led onto the following to achieve my
financial
year "04/05".

Financial year1: Format(DateAdd("m",-3,[Date1]),"yy")

Financial year2: Format(DateAdd("m",+6,[Date1]),"yy")

Financial year: [Financial year1]+"/"+[Financial year2]


KARL DEWEY said:
When does your fiscal year start? October? April? July?
If it is October the add 3 months to the date and format as year.
"FY " & Format(DateAdd("m",3,[YourDateField]),"yyyy")
April wouls subtract 3 months.
"FY " & Format(DateAdd("m",-3,[YourDateField]),"yyyy")
--
KARL DEWEY
Build a little - Test a little


tiff said:
I need to calculate the financial years from a table with over 10 years worth
of data for comparison, cannot use IIF as too many variables, anybody got a
funky shorthand way of doing this?
Click to expand...
Click to expand...
 
I think you misunderstood. What I gave you translates your calendar date
records into a Fiscal Year. You would use FY criteria to pull records for
the fiscal year.
Financial Year: Format(DateAdd("m",-3,[Date1]),"yyyy")

Example 11/11/2006 would fall out as being in FY07.

Your +6 will not work for Financial year2.


--
KARL DEWEY
Build a little - Test a little


tiff said:
Cheers, that worked a treat and then led onto the following to achieve my
financial
year "04/05".

Financial year1: Format(DateAdd("m",-3,[Date1]),"yy")

Financial year2: Format(DateAdd("m",+6,[Date1]),"yy")

Financial year: [Financial year1]+"/"+[Financial year2]


KARL DEWEY said:
When does your fiscal year start? October? April? July?
If it is October the add 3 months to the date and format as year.
"FY " & Format(DateAdd("m",3,[YourDateField]),"yyyy")
April wouls subtract 3 months.
"FY " & Format(DateAdd("m",-3,[YourDateField]),"yyyy")
--
KARL DEWEY
Build a little - Test a little


tiff said:
I need to calculate the financial years from a table with over 10 years worth
of data for comparison, cannot use IIF as too many variables, anybody got a
funky shorthand way of doing this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top