Complicated Date formula
 

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

Complicated Date formula
 

Is there a pattern in the way you name your sheets?
--
Thanks,
Shane Devenshire


"Scady" wrote:

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

Complicated Date formula
 

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.

--
Regards

Roger Govier


"Scady" wrote in message
...
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

Complicated Date formula
 

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.....

"ShaneDevenshire" wrote:

Is there a pattern in the way you name your sheets?
--
Thanks,
Shane Devenshire


"Scady" wrote:

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

Complicated Date formula
 

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" wrote:

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.

--
Regards

Roger Govier


"Scady" wrote in message
...
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

Complicated Date formula
 

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!

--
Regards

Roger Govier


"Scady" wrote in message
...
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" wrote:

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.

--
Regards

Roger Govier


"Scady" wrote in message
...
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

Complicated Date formula
 

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?

--
Cheers,
Shane Devenshire


"Roger Govier" wrote:

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!

--
Regards

Roger Govier


"Scady" wrote in message
...
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" wrote:

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.

--
Regards

Roger Govier


"Scady" wrote in message
...
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