Complicated Date formula

[Guest]

Hello. I am trying to come up with a solution for formatting a date
correctly on individual sheets.

I have a spreadsheet that is used to track availabilty on a piece of
equipment during a contract period. There are seven years in the contract
and I have a sheet for each month of each year. The contract always starts
on the first of the month. Sheet one is January, Period 1. Sheet two is
February, Period 1 (period 1 being the first year.) I have a cell at the top
of each sheet that show the date for that sheet. I am trying to populate
that cell in each sheet by entering a "contract start date" in a setup sheet
I have at the end of all the other sheets. The problem I run into is that if
the contract starts on June 1, 2007 for example, I cannot get sheet one
(which would actually end up being January 2008) to get the date correct.
Can anyone offer some advice?

Thanks.

Scott

 

[Guest]

Is there a pattern in the way you name your sheets?

 

[Roger Govier]

Hi

Is there any particular reason why you have to use 84 sheets for each
contract?
It seems rather excessive and I would have thought there would be ways
to reduce this (and maybe eliminate your problem at the same time).

Post back with more information about what you are recording on each
sheet, and maybe we can give you some ideas on simplifying the
procedure.

 

[Guest]

The sheets are all named "AV January Period 1, CST January Period 1, AV
February Period 1, CST Febraury Period 1, AV March Period 1, CST March Period
1, etc.....

 

[Guest]

Each sheet is for a month in the contract. On each sheet, we track operating
hours, idle time, contract down time, customer down time, availabilty
percentages, fuel usage etc.. We used to have a seperate workbook for each
"period" (year) in the contract but that meant keeping track of 7 files.
That is why we moved them into one file.

 

[Roger Govier]

Sure, but why not just a sheet per year?
Adding one extra column to record Month against any entry would then
enable you to use Autofilter to bring up any individual Month's data.
With a few rows inserted at the top of the sheet and the use of the
Subtotal() function, you could show Totals, Averages etc for any of the
columns, for the filtered data in view.

Just a thought!

 

[Guest]

I'll return to the original issue. The naming convention you indicated has a
problem - there is no indicator in the sheet names for years, so after the
first year your workbook is over?