|
calander
Ideally I'd like to create a calandar like this:
1. Put "01/02/04" in a cell A1 and in cells B1 to B35 I get returned each day in Feb listed eg: Mon 1st Feb, Tue 2nd Feb etc. 2. I don't want to run into March so I can't use 1/2/04 +1, +1....+31 because I'll get 3rd March in the Feb calculation, but 31st Oct in the Oct calculation! 3. Can I do the above but only show working days (Mon-Fri). 4. Finally. Can I draw a line at the start of every Monday. Any help to any of the above gratefully recieved Many thanks -- Peter London, UK |
Peter,
Put this formula in A2 and copy down to A31 =IF(A1<"",IF(MONTH(A1+1)=MONTH($A$1),A1+1,""),"") and format as "ddd d", doesn't show the ordinal value I am afraid. If you only want working days, add conditional formatting and a formula of =WEEKDAY(A1,2)5 and give a font colour of white -- HTH RP (remove nothere from the email address if mailing direct) "Peter" wrote in message ... Ideally I'd like to create a calandar like this: 1. Put "01/02/04" in a cell A1 and in cells B1 to B35 I get returned each day in Feb listed eg: Mon 1st Feb, Tue 2nd Feb etc. 2. I don't want to run into March so I can't use 1/2/04 +1, +1....+31 because I'll get 3rd March in the Feb calculation, but 31st Oct in the Oct calculation! 3. Can I do the above but only show working days (Mon-Fri). 4. Finally. Can I draw a line at the start of every Monday. Any help to any of the above gratefully recieved Many thanks -- Peter London, UK |
Great answer Bob. Worked very well. Thanks.
One more thing. How can I show only weekdays eg: 20 working days in a month with no spaces. Possible? Many thanks Peter "Bob Phillips" wrote: Peter, Put this formula in A2 and copy down to A31 =IF(A1<"",IF(MONTH(A1+1)=MONTH($A$1),A1+1,""),"") and format as "ddd d", doesn't show the ordinal value I am afraid. If you only want working days, add conditional formatting and a formula of =WEEKDAY(A1,2)5 and give a font colour of white -- HTH RP (remove nothere from the email address if mailing direct) "Peter" wrote in message ... Ideally I'd like to create a calandar like this: 1. Put "01/02/04" in a cell A1 and in cells B1 to B35 I get returned each day in Feb listed eg: Mon 1st Feb, Tue 2nd Feb etc. 2. I don't want to run into March so I can't use 1/2/04 +1, +1....+31 because I'll get 3rd March in the Feb calculation, but 31st Oct in the Oct calculation! 3. Can I do the above but only show working days (Mon-Fri). 4. Finally. Can I draw a line at the start of every Monday. Any help to any of the above gratefully recieved Many thanks -- Peter London, UK |
You can put the first date in the top cell, then used Edit/Fill/Series to fill
the cells: specify the stop date and weekdays only. On Mon, 6 Dec 2004 01:43:01 -0800, "Peter" wrote: Great answer Bob. Worked very well. Thanks. One more thing. How can I show only weekdays eg: 20 working days in a month with no spaces. Possible? Many thanks Peter "Bob Phillips" wrote: Peter, Put this formula in A2 and copy down to A31 =IF(A1<"",IF(MONTH(A1+1)=MONTH($A$1),A1+1,""),"") and format as "ddd d", doesn't show the ordinal value I am afraid. If you only want working days, add conditional formatting and a formula of =WEEKDAY(A1,2)5 and give a font colour of white -- HTH RP (remove nothere from the email address if mailing direct) "Peter" wrote in message ... Ideally I'd like to create a calandar like this: 1. Put "01/02/04" in a cell A1 and in cells B1 to B35 I get returned each day in Feb listed eg: Mon 1st Feb, Tue 2nd Feb etc. 2. I don't want to run into March so I can't use 1/2/04 +1, +1....+31 because I'll get 3rd March in the Feb calculation, but 31st Oct in the Oct calculation! 3. Can I do the above but only show working days (Mon-Fri). 4. Finally. Can I draw a line at the start of every Monday. Any help to any of the above gratefully recieved Many thanks -- Peter London, UK |
Thanks Myrna.
"Myrna Larson" wrote: You can put the first date in the top cell, then used Edit/Fill/Series to fill the cells: specify the stop date and weekdays only. On Mon, 6 Dec 2004 01:43:01 -0800, "Peter" wrote: Great answer Bob. Worked very well. Thanks. One more thing. How can I show only weekdays eg: 20 working days in a month with no spaces. Possible? Many thanks Peter "Bob Phillips" wrote: Peter, Put this formula in A2 and copy down to A31 =IF(A1<"",IF(MONTH(A1+1)=MONTH($A$1),A1+1,""),"") and format as "ddd d", doesn't show the ordinal value I am afraid. If you only want working days, add conditional formatting and a formula of =WEEKDAY(A1,2)5 and give a font colour of white -- HTH RP (remove nothere from the email address if mailing direct) "Peter" wrote in message ... Ideally I'd like to create a calandar like this: 1. Put "01/02/04" in a cell A1 and in cells B1 to B35 I get returned each day in Feb listed eg: Mon 1st Feb, Tue 2nd Feb etc. 2. I don't want to run into March so I can't use 1/2/04 +1, +1....+31 because I'll get 3rd March in the Feb calculation, but 31st Oct in the Oct calculation! 3. Can I do the above but only show working days (Mon-Fri). 4. Finally. Can I draw a line at the start of every Monday. Any help to any of the above gratefully recieved Many thanks -- Peter London, UK |
Peter,
Yes, I shbould have given you this to start with In A2: =IF(A1<"",IF(MONTH(WORKDAY(A1,1))=MONTH($A$1),WOR KDAY(A1,1),""),"") anmd copy down. You can even ignore holidays if you put the holiday dates in tabel and b=name it say 'holidays' with =IF(A1<"",IF(MONTH(WORKDAY(A1,1,holidays))=MONTH( $A$1),WORKDAY(A1,1,holiday s),""),"") Myrna's solution is only good once, if you change the dat5e in A1, you have to re-do it. -- HTH RP (remove nothere from the email address if mailing direct) "Peter" wrote in message ... Great answer Bob. Worked very well. Thanks. One more thing. How can I show only weekdays eg: 20 working days in a month with no spaces. Possible? Many thanks Peter "Bob Phillips" wrote: Peter, Put this formula in A2 and copy down to A31 =IF(A1<"",IF(MONTH(A1+1)=MONTH($A$1),A1+1,""),"") and format as "ddd d", doesn't show the ordinal value I am afraid. If you only want working days, add conditional formatting and a formula of =WEEKDAY(A1,2)5 and give a font colour of white -- HTH RP (remove nothere from the email address if mailing direct) "Peter" wrote in message ... Ideally I'd like to create a calandar like this: 1. Put "01/02/04" in a cell A1 and in cells B1 to B35 I get returned each day in Feb listed eg: Mon 1st Feb, Tue 2nd Feb etc. 2. I don't want to run into March so I can't use 1/2/04 +1, +1....+31 because I'll get 3rd March in the Feb calculation, but 31st Oct in the Oct calculation! 3. Can I do the above but only show working days (Mon-Fri). 4. Finally. Can I draw a line at the start of every Monday. Any help to any of the above gratefully recieved Many thanks -- Peter London, UK |
| All times are GMT +1. The time now is 11:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com