Fiscal year

[DIH]

My company recently went to a 4-4-5 week calendar fiscal year (28 days
first month, 28 days second month and 35 days third month etc throughout
the fiscal year). Needless to say, all my queries and reports are based
on the regular month end dates. Below is the layout for fiscal 2009. I'm
at a complete loss about how to convert all my queries / reports to this
new way of doing week / month / year to date calculations.

To give some details:
The main table is called "DAILY SHIFT". This table contains product
codes that were run, the date, how many units and the theoretical number
of units (as well as other metrics). To keep this post as short as
possible,the main idea is to get efficiency percentages by dividing the
"ACTUAL" units by the "THEORETICAL". Data is entered for every day.
I have a form where the user simply clicks a date in the calendar
control, and the report will give the month to date figures (e.g. The
user selects 11/14/2008 and the report will show the summed data from
11/1/2008 to 11/14/2008. The new month to date would return data from
10/26/2008 to 11/14/2008. To make matters more interesting, my boss now
also wants week to date, quarter to date and year to date as well.

Shortened "DAILY SHIFT" table example:

DATE Line Code Actual Theo (other metrics...)
11/16/2008 TOW1 21855 500 900
11/16/2008 TOW3 74512 700 1100
11/17/2008 TOW4 30157 1500 2200
..
..
..


Here is the fiscal layout for 2009:

Period Month FirstDay LastDay LastDayEnd Weeks Days Quarter
1 Oct 10/01/08 10/25/08 Sat 3.6 25
2 Nov 10/26/08 11/22/08 Sat 4.0 28
3 Dec 11/23/08 12/27/08 Sat 5.0 35 1
4 Jan 12/28/08 01/24/09 Sat 4.0 28
5 Feb 01/25/09 02/21/09 Sat 4.0 28
6 Mar 02/22/09 03/28/09 Sat 5.0 35 2
7 Apr 03/29/09 04/25/09 Sat 4.0 28
8 May 04/26/09 05/23/09 Sat 4.0 28
9 Jun 05/24/09 06/27/09 Sat 5.0 35 3
10 Jul 06/28/09 07/25/09 Sat 4.0 28
11 Aug 07/26/09 08/22/09 Sat 4.0 28
12 Sep 08/23/09 09/26/09 Sat 5.0 35 4

I know this is alot to ask, but any insight would greatly be
appreciated. Also, please let me know if there is anything I left out
that could be useful.

Dave

 

[NetworkTrade]

never heard of this, seems arbitrary and absurd - I hope it is not a
spreading norm in business....

obviously all queries for time ranged reports need to have their date range
method redefined.......since a month is not a month... it is not dissimilar
to 'payroll periods' which often straddle months/weeks

a headache indeed....but only in the summary reporting area...collection of
data stored in tables is granular at the daily level so that isn't affected.

 

[John W. Vinson]

My company recently went to a 4-4-5 week calendar fiscal year (28 days
first month, 28 days second month and 35 days third month etc throughout
the fiscal year). Needless to say, all my queries and reports are based
on the regular month end dates. Below is the layout for fiscal 2009. I'm
at a complete loss about how to convert all my queries / reports to this
new way of doing week / month / year to date calculations.

I'd really suggest a calendar translation table with a Date/Time field (for
the actual calendar date), and fields for the fiscal month, fiscal week and
fiscal year for that date. It would probably be pretty easy to build this
table in code, but even easier to build it in Excel and import it into a
table.

You can then Join a table with a date field to the translation table to
ascertain the pointy-headed bookkeeper's mystical date.