Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Please help | Excel Worksheet Functions | |||
Trending Formula Results by Date | Excel Worksheet Functions | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions |