Calendar 1st month ok but 2nd to 12th how.
Hello from Steved
Below I got from this forum Ok I done the below but also create 11 extra months. My calendar is 4 across by 3 down giving me 12months. Ok all 12 calendars has the same month on it, how do I change the next month to february and all the others until I have December please.Thanks oh yes one more thing I've changed NOW() to A1 being the first day off the new year 01/01/06. =IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))- MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)- (WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+ {0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",DATE(YEAR(NOW()), MONTH(NOW()),1)-(WEEKDAY(DATE(YEAR(NOW()), MONTH(NOW()),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1) To use it: 1. Copy the formula text to the clipboard 2. Activate a sheet and select a 7-col by 6-row range 3. Press F2 4. Press Ctrl+V to paste the formula into the active cell 5. Press Ctrl+Shift+Enter (to make it a multicell array formula) 6. Format the cells using the "d" number format. |
Not sure if you're implementing it correctly, Steved ..
Had a play with the "calendar" array formula you found and created a demo file with it (the formula's fantastic!) Here's a link: http://cjoint.com/?jCjnufCfrH WorksheetCalendar.xls and here's what I did .. In Sheet1 ----------- Created a DV droplist in say, F1, via: Data Validation Allow: List Source range: =MthYr where MthYr is a named range in another sheet: DV housing the 1st of month dates for 3 years (2005 - 2007) The DV in F1 will allow us to select the desired month-year for the calendar to be set-up in B3:H9 Placed "day" labels into B3:H3: Sun, Mon, ... Sat Selected a 7C x 6R range, i.e. B4:H9 Placed in the formula bar and array-entered with CTRL+SHIFT+ENTER: =IF(MONTH(DATE(YEAR(F1),MONTH(F1),1))-MONTH(DATE(YEAR(F1), MONTH(F1),1)-(WEEKDAY(DATE(YEAR(F1),MONTH(F1),1))-1)+ {0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"", DATE(YEAR(F1),MONTH(F1),1)-(WEEKDAY(DATE(YEAR(F1), MONTH(F1),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1) (The amended array formula points to the DV cell in F1) Sheet1 with the calendar created can then be copied as desired -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Steved" wrote in message ... Hello from Steved Below I got from this forum Ok I done the below but also create 11 extra months. My calendar is 4 across by 3 down giving me 12months. Ok all 12 calendars has the same month on it, how do I change the next month to february and all the others until I have December please.Thanks oh yes one more thing I've changed NOW() to A1 being the first day off the new year 01/01/06. =IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))- MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)- (WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+ {0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",DATE(YEAR(NOW()), MONTH(NOW()),1)-(WEEKDAY(DATE(YEAR(NOW()), MONTH(NOW()),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1) To use it: 1. Copy the formula text to the clipboard 2. Activate a sheet and select a 7-col by 6-row range 3. Press F2 4. Press Ctrl+V to paste the formula into the active cell 5. Press Ctrl+Shift+Enter (to make it a multicell array formula) 6. Format the cells using the "d" number format. |
Another link: http://www.savefile.com/files/4349494
WorksheetCalendar.xls -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Hi Max
I was playing about with this for another poster in this group earlier in the month (20/09). He also wanted to make the calendar start on a Monday, rather than a Sunday. I amended the 2 occurrences of (WEEKDAY(DATE(YEAR(F1),MONTH(F1),1))-1) to (WEEKDAY(DATE(YEAR(F1),MONTH(F1),1))-2) which makes it start on a Monday instead. However, it works well for months other than May-05, where the 1st is a Sunday, and only shows dates from 2nd through 31st. I guess the same applies to any month beginning on a Sunday, but I have spent some time trying to figure out how to amend the formula to correct this, but I have failed miserably. Perhaps you can see the way. Regards Roger Govier Max wrote: Not sure if you're implementing it correctly, Steved .. Had a play with the "calendar" array formula you found and created a demo file with it (the formula's fantastic!) Here's a link: http://cjoint.com/?jCjnufCfrH WorksheetCalendar.xls and here's what I did .. In Sheet1 ----------- Created a DV droplist in say, F1, via: Data Validation Allow: List Source range: =MthYr where MthYr is a named range in another sheet: DV housing the 1st of month dates for 3 years (2005 - 2007) The DV in F1 will allow us to select the desired month-year for the calendar to be set-up in B3:H9 Placed "day" labels into B3:H3: Sun, Mon, ... Sat Selected a 7C x 6R range, i.e. B4:H9 Placed in the formula bar and array-entered with CTRL+SHIFT+ENTER: =IF(MONTH(DATE(YEAR(F1),MONTH(F1),1))-MONTH(DATE(YEAR(F1), MONTH(F1),1)-(WEEKDAY(DATE(YEAR(F1),MONTH(F1),1))-1)+ {0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"", DATE(YEAR(F1),MONTH(F1),1)-(WEEKDAY(DATE(YEAR(F1), MONTH(F1),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1) (The amended array formula points to the DV cell in F1) Sheet1 with the calendar created can then be copied as desired -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Steved" wrote in message ... Hello from Steved Below I got from this forum Ok I done the below but also create 11 extra months. My calendar is 4 across by 3 down giving me 12months. Ok all 12 calendars has the same month on it, how do I change the next month to february and all the others until I have December please.Thanks oh yes one more thing I've changed NOW() to A1 being the first day off the new year 01/01/06. =IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))- MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)- (WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+ {0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",DATE(YEAR(NOW()), MONTH(NOW()),1)-(WEEKDAY(DATE(YEAR(NOW()), MONTH(NOW()),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1) To use it: 1. Copy the formula text to the clipboard 2. Activate a sheet and select a 7-col by 6-row range 3. Press F2 4. Press Ctrl+V to paste the formula into the active cell 5. Press Ctrl+Shift+Enter (to make it a multicell array formula) 6. Format the cells using the "d" number format. |
.. Perhaps you can see the way
No, I'm afraid not <g .. , but I did try-out a cheat (reading Bernie's closing lines in: http://tinyurl.com/dvskz ) which seems to work ok in Sheet2 in the attached revised file: WorksheetCalendar_2.xls re- links at either: http://cjoint.com/?jCk6l8Wz3z http://www.savefile.com/files/2959578 Sheet2 has the same array formula as in Sheet1, but with a cheat attempt <g to show calendar starting with Monday An additional col I is created to link to the hidden col B, with a "pull up" of numbers into col I Placed in I4: =IF($B$4<"",OFFSET($B$4,ROWS($A$1:A1)-1,),OFFSET($B$4,ROWS($A$1:A1),)) I4 is copied down to I8 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Hi Max
I hadn't seen Bernie's posting. Cheating..? No, just bending to fit the OP's requirements. Knowing that neither you nor Bernie could solve it directly makes me feel a whole heap better somehow!!!! Thanks for the response. Regards Roger Govier Max wrote: .. Perhaps you can see the way No, I'm afraid not <g .. , but I did try-out a cheat (reading Bernie's closing lines in: http://tinyurl.com/dvskz ) which seems to work ok in Sheet2 in the attached revised file: WorksheetCalendar_2.xls re- links at either: http://cjoint.com/?jCk6l8Wz3z http://www.savefile.com/files/2959578 Sheet2 has the same array formula as in Sheet1, but with a cheat attempt <g to show calendar starting with Monday An additional col I is created to link to the hidden col B, with a "pull up" of numbers into col I Placed in I4: =IF($B$4<"",OFFSET($B$4,ROWS($A$1:A1)-1,),OFFSET($B$4,ROWS($A$1:A1),)) I4 is copied down to I8 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
You're welcome, Roger !
Cheers -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Hello Max from Steved
Max I used your formula below And have got 4 by 3 calendar = 12 months =DATE(YEAR(L1),MONTH(L1)+1,DAY(L1)) I've used the above for the Month Titles The objective is it to fit on a A4 Sheet Landscape and in Cell A1 i've got 01/01/2006 Now all I do is change the date in A1 and bingo it updates I thankyou very much for your formula. "Max" wrote: Not sure if you're implementing it correctly, Steved .. Had a play with the "calendar" array formula you found and created a demo file with it (the formula's fantastic!) Here's a link: http://cjoint.com/?jCjnufCfrH WorksheetCalendar.xls and here's what I did .. In Sheet1 ----------- Created a DV droplist in say, F1, via: Data Validation Allow: List Source range: =MthYr where MthYr is a named range in another sheet: DV housing the 1st of month dates for 3 years (2005 - 2007) The DV in F1 will allow us to select the desired month-year for the calendar to be set-up in B3:H9 Placed "day" labels into B3:H3: Sun, Mon, ... Sat Selected a 7C x 6R range, i.e. B4:H9 Placed in the formula bar and array-entered with CTRL+SHIFT+ENTER: =IF(MONTH(DATE(YEAR(F1),MONTH(F1),1))-MONTH(DATE(YEAR(F1), MONTH(F1),1)-(WEEKDAY(DATE(YEAR(F1),MONTH(F1),1))-1)+ {0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"", DATE(YEAR(F1),MONTH(F1),1)-(WEEKDAY(DATE(YEAR(F1), MONTH(F1),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1) (The amended array formula points to the DV cell in F1) Sheet1 with the calendar created can then be copied as desired -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Steved" wrote in message ... Hello from Steved Below I got from this forum Ok I done the below but also create 11 extra months. My calendar is 4 across by 3 down giving me 12months. Ok all 12 calendars has the same month on it, how do I change the next month to february and all the others until I have December please.Thanks oh yes one more thing I've changed NOW() to A1 being the first day off the new year 01/01/06. =IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))- MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)- (WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+ {0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",DATE(YEAR(NOW()), MONTH(NOW()),1)-(WEEKDAY(DATE(YEAR(NOW()), MONTH(NOW()),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1) To use it: 1. Copy the formula text to the clipboard 2. Activate a sheet and select a 7-col by 6-row range 3. Press F2 4. Press Ctrl+V to paste the formula into the active cell 5. Press Ctrl+Shift+Enter (to make it a multicell array formula) 6. Format the cells using the "d" number format. |
Glad you sorted out out whatever it was that you wanted, Steved !
-- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
All times are GMT +1. The time now is 10:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com