Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calender Generation
Hi I was wondering if you could please help
I have made a calender in Excel for this year 2008 on sheet 1 it just shows a table devided into 12 months with the relevent dates My Question is can I get it to generate a calender 2009 with the with the correct date on automatically on sheet 2 and so on Thanks Joel -- N/A |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calender Generation
Joel,
The answer to this one is filled with a lot of "if"s , "but"s and "maybe"s. Basically the answer is yes, but how easy it would be to do depends much on your current calendar layout. John Walkenbach has a massive array formula published in some of his books on Excel that will automatically provide a calendar for any month/year combination. The 'problem' with it is that the cells are occupied by the formula/date and you can't type notes into the cells - but for printing a calendar for desk or wall, it's great. For setting up calendars to have a date on one row with note area underneath, things get a bit more complicated, but still do-able. Mostly this would all be handled by VBA code (if I did it - as I have in the past). It's almost easier to do it manually - start with a calendar for January and enter the date for January 1st manually, then in the rest of the month's cells you put a formula that adds 1 day to the previous day's date for each day of the month. For Feb thru Dec, you go to the proper day of the week in that month's calendar area and add 1 day to the last day of the previous month, then continue with the add 1 day formula for the rest of it. As I said, this can also be done with VBA code or with an array formula such as Walkenbach has published. "Joel" wrote: Hi I was wondering if you could please help I have made a calender in Excel for this year 2008 on sheet 1 it just shows a table devided into 12 months with the relevent dates My Question is can I get it to generate a calender 2009 with the with the correct date on automatically on sheet 2 and so on Thanks Joel -- N/A |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calender Generation
Thank you very mouch for your very detailed reply it is most appreciiated not
many people bother replying like that anymore. It is for general interest only so I might take a look at his book if I can find it Thanks again Joel -- N/A "JLatham" wrote: Joel, The answer to this one is filled with a lot of "if"s , "but"s and "maybe"s. Basically the answer is yes, but how easy it would be to do depends much on your current calendar layout. John Walkenbach has a massive array formula published in some of his books on Excel that will automatically provide a calendar for any month/year combination. The 'problem' with it is that the cells are occupied by the formula/date and you can't type notes into the cells - but for printing a calendar for desk or wall, it's great. For setting up calendars to have a date on one row with note area underneath, things get a bit more complicated, but still do-able. Mostly this would all be handled by VBA code (if I did it - as I have in the past). It's almost easier to do it manually - start with a calendar for January and enter the date for January 1st manually, then in the rest of the month's cells you put a formula that adds 1 day to the previous day's date for each day of the month. For Feb thru Dec, you go to the proper day of the week in that month's calendar area and add 1 day to the last day of the previous month, then continue with the add 1 day formula for the rest of it. As I said, this can also be done with VBA code or with an array formula such as Walkenbach has published. "Joel" wrote: Hi I was wondering if you could please help I have made a calender in Excel for this year 2008 on sheet 1 it just shows a table devided into 12 months with the relevent dates My Question is can I get it to generate a calender 2009 with the with the correct date on automatically on sheet 2 and so on Thanks Joel -- N/A |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calender Generation
I think this is what you meant.
http://j-walk.com/ss/excel/files/calarray.exe (from http://j-walk.com/ss/excel/files/general.htm) HTH, JP On Jan 28, 8:38*am, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: Joel, The answer to this one is filled with a lot of "if"s , "but"s and "maybe"s.. * Basically the answer is yes, but how easy it would be to do depends much on your current calendar layout. *John Walkenbach has a massive array formula published in some of his books on Excel that will automatically provide a calendar for any month/year combination. *The 'problem' with it is that the cells are occupied by the formula/date and you can't type notes into the cells - but for printing a calendar for desk or wall, it's great. For setting up calendars to have a date on one row with note area underneath, things get a bit more complicated, but still do-able. *Mostly this would all be handled by VBA code (if I did it - as I have in the past). It's almost easier to do it manually - start with a calendar for January and enter the date for January 1st manually, then in the rest of the month's cells you put a formula that adds 1 day to the previous day's date for each day of the month. *For Feb thru Dec, you go to the proper day of the week in that month's calendar area and add 1 day to the last day of the previous month, then continue with the add 1 day formula for the rest of it. *As I said, this can also be done with VBA code or with an array formula such as Walkenbach has published. "Joel" wrote: Hi I was wondering if you could please help I have made a calender in Excel for this year 2008 on sheet 1 it just shows a table devided into 12 months with the relevent dates My Question is can I get it to generate a calender 2009 with the with the correct date on automatically on sheet 2 and so on Thanks Joel -- N/A- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calender Generation
JP - good deal, that's probably exactly what I was thinking of (I didn't go
so far as to actually open it). Good to know that JW has published it for general consumption - I was hesitant to try to copy it from the book or CD (copyright rules and common courtesy and all like that, you know?). Joel: If that formula is the one I think it is, it's a doozy! It is an 'array' formula which means that you choose all of the cells you want it to be in first, then copy and paste the formula into the formula bar, and then you commit the formula by using the [Ctrl]+[Shift]+[Enter] keys (all pressed at once) instead of just the [Enter] key. Once you do that the formula will show up within curley braces: {} If you ever have to edit an array formula, you must recommit it with the same [Ctrl]+[Shift]+[Enter] keypress to keep it as an array formula. "JP" wrote: I think this is what you meant. http://j-walk.com/ss/excel/files/calarray.exe (from http://j-walk.com/ss/excel/files/general.htm) HTH, JP On Jan 28, 8:38 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: Joel, The answer to this one is filled with a lot of "if"s , "but"s and "maybe"s.. Basically the answer is yes, but how easy it would be to do depends much on your current calendar layout. John Walkenbach has a massive array formula published in some of his books on Excel that will automatically provide a calendar for any month/year combination. The 'problem' with it is that the cells are occupied by the formula/date and you can't type notes into the cells - but for printing a calendar for desk or wall, it's great. For setting up calendars to have a date on one row with note area underneath, things get a bit more complicated, but still do-able. Mostly this would all be handled by VBA code (if I did it - as I have in the past). It's almost easier to do it manually - start with a calendar for January and enter the date for January 1st manually, then in the rest of the month's cells you put a formula that adds 1 day to the previous day's date for each day of the month. For Feb thru Dec, you go to the proper day of the week in that month's calendar area and add 1 day to the last day of the previous month, then continue with the add 1 day formula for the rest of it. As I said, this can also be done with VBA code or with an array formula such as Walkenbach has published. "Joel" wrote: Hi I was wondering if you could please help I have made a calender in Excel for this year 2008 on sheet 1 it just shows a table devided into 12 months with the relevent dates My Question is can I get it to generate a calender 2009 with the with the correct date on automatically on sheet 2 and so on Thanks Joel -- N/A- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calender Generation
Eh, I just use one from MSFT:
http://tinyurl.com/3eydbj Thx, JP On Jan 28, 4:33*pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: JP - good deal, that's probably exactly what I was thinking of (I didn't go so far as to actually open it). *Good to know that JW has published it for general consumption - I was hesitant to try to copy it from the book or CD (copyright rules and common courtesy and all like that, you know?). Joel: If that formula is the one I think it is, it's a doozy! *It is an 'array' formula which means that you choose all of the cells you want it to be in first, then copy and paste the formula into the formula bar, and then you commit the formula by using the [Ctrl]+[Shift]+[Enter] keys (all pressed at once) instead of just the [Enter] key. *Once you do that the formula will show up within curley braces: {} If you ever have to edit an array formula, you must recommit it with the same [Ctrl]+[Shift]+[Enter] keypress to keep it as an array formula. "JP" wrote: I think this is what you meant. http://j-walk.com/ss/excel/files/calarray.exe(from http://j-walk.com/ss/excel/files/general.htm) HTH, JP On Jan 28, 8:38 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: Joel, The answer to this one is filled with a lot of "if"s , "but"s and "maybe"s.. * Basically the answer is yes, but how easy it would be to do depends much on your current calendar layout. *John Walkenbach has a massive array formula published in some of his books on Excel that will automatically provide a calendar for any month/year combination. *The 'problem' with it is that the cells are occupied by the formula/date and you can't type notes into the cells - but for printing a calendar for desk or wall, it's great. For setting up calendars to have a date on one row with note area underneath, things get a bit more complicated, but still do-able. *Mostly this would all be handled by VBA code (if I did it - as I have in the past). It's almost easier to do it manually - start with a calendar for January and enter the date for January 1st manually, then in the rest of the month's cells you put a formula that adds 1 day to the previous day's date for each day of the month. *For Feb thru Dec, you go to the proper day of the week in that month's calendar area and add 1 day to the last day of the previous month, then continue with the add 1 day formula for the rest of it. *As I said, this can also be done with VBA code or with an array formula such as Walkenbach has published. "Joel" wrote: Hi I was wondering if you could please help I have made a calender in Excel for this year 2008 on sheet 1 it just shows a table devided into 12 months with the relevent dates My Question is can I get it to generate a calender 2009 with the with the correct date on automatically on sheet 2 and so on Thanks Joel -- N/A- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Generation | Excel Worksheet Functions | |||
Key Generation | Excel Discussion (Misc queries) | |||
Data Generation | Excel Discussion (Misc queries) | |||
email generation | Excel Discussion (Misc queries) | |||
Random Name Generation | Excel Worksheet Functions |