calendar from formula

[mattrx731]

I'm using this formula from

http://www.cpearson.com/excel/DateTimeWS.htm

Nth Day Of Week For A Month And Year

This formula will return will return the date of Nth day-of-week for a given month and year. For example, it will return 26-March-98 for the 4th Thursday of March, 1998. Days-of-week range from 1 to 7, with Sunday = 1 and Saturday = 7.

=DATE(Yr,Mon,1+((Nth-(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+
(DoW-WEEKDAY(DATE(Yr,Mon,1))))

Where Yr, Mon, Nth, and DoW are cell references or values indicating Year, Month, Nth, and Day-Of-Week.

I would like to be able to change the year and month in A2 and B2 and have the calendar change.
I will be inserting rows between the weeks to return appts, if I can get this part working.
I could make a new tab for each month, but I thought I would give this a try.

Excel Workbook
	A	B	C	D	E	F	G	H
1	year	month	*	*	*	*	*	*
2	2008	10	*	*	*	*	*	*
3	*	*	*	*	*	*	*	*
4	*	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
5	*	1	2	3	4	5	6	7
6	1	10/5/2008	10/6/2008	10/7/2008	10/1/2008	10/2/2008	10/3/2008	10/4/2008
7	2	10/12/2008	10/13/2008	10/14/2008	10/8/2008	10/9/2008	10/10/2008	10/11/2008
8	3	10/19/2008	10/20/2008	10/21/2008	10/15/2008	10/16/2008	10/17/2008	10/18/2008
9	4	10/26/2008	10/27/2008	10/28/2008	10/22/2008	10/23/2008	10/24/2008	10/25/2008
10	5	11/2/2008	11/3/2008	11/4/2008	10/29/2008	10/30/2008	10/31/2008	11/1/2008
Sheet1

As you can see B6, C6 and D6 should be blank.
The 2nd week (B7) should start with 10/5/08.
I've tried a few different If formulas in the second week, but can't seem to get it too work.

Any ideas would be great
Thanks
Matt

 

[menor59]

Question...Is there a way to get it to Highlight Todays Date?? and when it becomes tomorrow...highlight tomorrows...etc.etc??

Rather than it say use =Today() Id like to use the 1st of the month... as the trigger in G13.

 

[tusharm]

You may have requirements beyond the below suggestion but if not...see
Calendar template
http://www.tushar-mehta.com/excel/templates/calendar/index.html

I'm using this formula from



I would like to be able to change the year and month in A2 and B2 and have the calendar change.
{snip}

Any ideas would be great
Thanks
Matt

 

[mattrx731]

Question...Is there a way to get it to Highlight Todays Date?? and when it becomes tomorrow...highlight tomorrows...etc.etc??

Rather than it say use =Today() Id like to use the 1st of the month... as the trigger in G13.

I think you need to just use conditional formatting to do this highlight.

Theozz...
I know this is the dreaded question, but, after getting yours to work great, I need to insert a row between each week. Obviouslly this messes up your formulas, but I can't figure out how to modify it to work... I'm guessing I need to change

=IF(MONTH($G$13+([COLOR="Red"]ROW()-15[/COLOR])*7+COLUMN()-WEEKDAY($G$13))=MONTH($G$13),$G$13+([COLOR="red"]ROW()-15[/COLOR])*7+COLUMN()-WEEKDAY($G$13),TEXT($G$13+([COLOR="red"]ROW()-15[/COLOR])*7+COLUMN()-WEEKDAY($G$13),"(dd)"))

But I'm not sure to what...

Thanks

 

[menor59]

Got it to work with Highlighting all the days in the month...then clicking Format, Conditional formating..Condition 1 Cell value is, then Equal to then typing =Today() , then clicking Fomat and changing the backround to a different color. Boom. it worked.

Side note...Im Locking and hidding the formulas on this workbook. But i want users to be able to add comments on days of the week. Can this be done some how so that they can right click and add a comment without unprotecting the worksheet?

 

[theozz]

At cell
A15 then ROW()-15
A16 then ROW()-16
and etc...

A15 then COLUMN()
B15 then COLUMN()-1

each weeks date should change by (ROW()-15)*7
each 1 day should change by COLUMN()
so +1 or -1 and other value change and test yourself

 

[riaz]

Riaz,

I don't know if this a formatting problem or difference between American & International dates, but October started on a wednesday this year. How do I adjust these formulas for that?
January 2008 is correct, but starting at Feb, 2008 it is at least one day off...

Hello everyone.

I wonder if anyone could shed light on this. Matt is correct in that when he opened my sheet, October gave the wrong date. When he sent me the file back, I got the same wrong date for October. However, if I change the months one at a time, it works correctly.

When I evaluate the formula if shows

IF(FALSE, #N/A,IF(TRUE, 39722,"*"))

The next step shows

IF(FALSE,#N/A,39722)

The final answer shows * which is patently incorrect.

If I change the month to 9, then back to 10, it works fine.

 

[menor59]

In the post its showing that this code should be used in A15:G10

=IF(MONTH($G$13+(ROW()-15)*7+COLUMN()-WEEKDAY($G$13))=MONTH($G$13),$G$13+(ROW()-15)*7+COLUMN()-WEEKDAY($G$13),TEXT($G$13+(ROW()-15)*7+COLUMN()-WEEKDAY($G$13),"(dd)"))

Instead of G13 Im using K31 at my refenence..and cells E33:K38 But everything is (##) am I doing something wrong...??

My code:

=IF(MONTH($K$31+(ROW()-15)*7+COLUMN()-WEEKDAY($K$31))=MONTH($K$31),$K$31+(ROW()-15)*7+COLUMN()-WEEKDAY($K$31),TEXT($K$31+(ROW()-15)*7+COLUMN()-WEEKDAY($K$31),"(dd)"))

This is cells E33:K38 with K31 having..

=DATE(YEAR(H31), MONTH(H31), 1)

and H31 having

=today()

 

[theozz]

Hi ! Here Suggest to you

<table style="border-collapse: separate;" colspan="2" border="7" cellspacing="0"><tbody bgcolor="#ffffff"><tr height="36"><td colspan="2"> Nice Question Thanks.
DataSheet= Sheet2</td></tr><tr><td align="right" width="30"><table rowspan="13" cellspacing="1" width="30"><tbody bgcolor="#ffffff"><tr height="18"><td>
</td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">30 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">31 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">32 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">33 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">34 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">35 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">36 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">37 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">38 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">39 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">40 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">41 </td></tr></tbody></table></td><td width="715"><table colspan="7" rowspan="6" bgcolor="#939393" border="0" cellspacing="1" width="715"><tbody bgcolor="#ffffff"><tr height="18"><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc" width="93">E</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc" width="93">F</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc" width="93">G</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc" width="123">H</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc" width="93">I</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc" width="93">J</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc" width="123">K</td></tr><tr height="18"><td colspan="1" align="left" bgcolor="#ffffff" width="93"> </td><td colspan="1" align="left" bgcolor="#ffffff" width="93"> </td><td colspan="1" align="left" bgcolor="#ffffff" width="93"> </td><td colspan="4" align="center" bgcolor="#ccffcc" width="434">H31,K31 His Format= yyyy-mm-dd</td></tr><tr height="18"><td colspan="1" align="left" bgcolor="#ffffff" width="93"> </td><td colspan="1" align="left" bgcolor="#ffffff" width="93"> </td><td colspan="1" align="left" bgcolor="#ffffff" width="93"> </td><td colspan="1" align="right" bgcolor="#ccffcc" width="123">2008-10-14</td><td colspan="1" align="left" bgcolor="#ffffff" width="93"> </td><td colspan="1" align="left" bgcolor="#ffffff" width="93"> </td><td colspan="1" align="right" bgcolor="#ccffcc" width="123">2008-10-01</td></tr><tr height="18"><td colspan="1" align="left" bgcolor="#ffffff" width="93">Sun</td><td colspan="1" align="left" bgcolor="#ffffff" width="93">Mon</td><td colspan="1" align="left" bgcolor="#ffffff" width="93">Tue</td><td colspan="1" align="left" bgcolor="#ffffff" width="123">Wed</td><td colspan="1" align="left" bgcolor="#ffffff" width="93">Thu</td><td colspan="1" align="left" bgcolor="#ffffff" width="93">Fri</td><td colspan="1" align="left" bgcolor="#ffffff" width="123">Sat</td></tr><tr height="18"><td colspan="1" align="left" bgcolor="#ccffcc" width="93">(28)</td><td colspan="1" align="left" bgcolor="#ccffcc" width="93">(29)</td><td colspan="1" align="left" bgcolor="#ccffcc" width="93">(30)</td><td colspan="1" align="right" bgcolor="#ccffcc" width="123">01</td><td colspan="1" align="right" bgcolor="#ccffcc" width="93">02</td><td colspan="1" align="right" bgcolor="#ccffcc" width="93">03</td><td colspan="1" align="right" bgcolor="#ccffcc" width="123">04</td></tr><tr height="18"><td colspan="1" align="right" bgcolor="#ccffcc" width="93">05</td><td colspan="1" align="right" bgcolor="#ccffcc" width="93">06</td><td colspan="1" align="right" bgcolor="#ccffcc" width="93">07</td><td colspan="1" align="right" bgcolor="#ccffcc" width="123">08</td><td colspan="1" align="right" bgcolor="#ccffcc" width="93">09</td><td colspan="1" align="right" bgcolor="#ccffcc" width="93">10</td><td colspan="1" align="right" bgcolor="#ccffcc" width="123">11</td></tr><tr height="18"><td colspan="1" align="right" bgcolor="#ccffcc" width="93">12</td><td colspan="1" align="right" bgcolor="#ccffcc" width="93">13</td><td colspan="1" align="right" bgcolor="#ccffcc" width="93">14</td><td colspan="1" align="right" bgcolor="#ccffcc" width="123">15</td><td colspan="1" align="right" bgcolor="#ccffcc" width="93">16</td><td colspan="1" align="right" bgcolor="#ccffcc" width="93">17</td><td colspan="1" align="right" bgcolor="#ccffcc" width="123">18</td></tr></tbody></table><table colspan="7" rowspan="6" bgcolor="#939393" border="0" cellspacing="1" width="715"><tbody bgcolor="#ffffff"><tr height="18"><td colspan="1" align="right" bgcolor="#ccffcc" width="93">19</td><td colspan="1" align="right" bgcolor="#ccffcc" width="93">20</td><td colspan="1" align="right" bgcolor="#ccffcc" width="93">21</td><td colspan="1" align="right" bgcolor="#ccffcc" width="123">22</td><td colspan="1" align="right" bgcolor="#ccffcc" width="93">23</td><td colspan="1" align="right" bgcolor="#ccffcc" width="93">24</td><td colspan="1" align="right" bgcolor="#ccffcc" width="123">25</td></tr><tr height="18"><td colspan="1" align="right" bgcolor="#ccffcc" width="93">26</td><td colspan="1" align="right" bgcolor="#ccffcc" width="93">27</td><td colspan="1" align="right" bgcolor="#ccffcc" width="93">28</td><td colspan="1" align="right" bgcolor="#ccffcc" width="123">29</td><td colspan="1" align="right" bgcolor="#ccffcc" width="93">30</td><td colspan="1" align="right" bgcolor="#ccffcc" width="93">31</td><td colspan="1" align="left" bgcolor="#ccffcc" width="123">(01)</td></tr><tr height="18"><td colspan="1" align="left" bgcolor="#ccffcc" width="93">(02)</td><td colspan="1" align="left" bgcolor="#ccffcc" width="93">(03)</td><td colspan="1" align="left" bgcolor="#ccffcc" width="93">(04)</td><td colspan="1" align="left" bgcolor="#ccffcc" width="123">(05)</td><td colspan="1" align="left" bgcolor="#ccffcc" width="93">(06)</td><td colspan="1" align="left" bgcolor="#ccffcc" width="93">(07)</td><td colspan="1" align="left" bgcolor="#ccffcc" width="123">(08)</td></tr><tr height="18"><td colspan="1" align="left" bgcolor="#ffffff" width="93"> </td><td colspan="1" align="left" bgcolor="#ffffff" width="93"> </td><td colspan="1" align="left" bgcolor="#ffffff" width="93"> </td><td colspan="1" align="left" bgcolor="#ffffff" width="123"> </td><td colspan="3" align="center" bgcolor="#ccffcc" width="310">E33:K38 His Format= dd</td></tr><tr height="18"><td colspan="7" align="left" bgcolor="#ffffff" width="715">First Cell is E33 so (ROW()-33) Why?? His row is 33</td></tr><tr height="18"><td colspan="7" align="left" bgcolor="#ffffff" width="715">First Cell is E33 so (COLUMN()-4) Why? His Column is 5</td></tr></tbody></table></td></tr></tbody></table>
<colspan=5 width="800" rowspan="5" height="90"></colspan=5><table style="border-collapse: separate;" border="6" cellspacing="0"><tbody><tr height="24"><td colspan="5" align="center">Used Formula ...(With Running MicrosoftExcel Ver 2003)</td></tr><tr height="24"><td align="center" bgcolor="#d3d3d3" width="4%">No</td><td align="center" bgcolor="#d3d3d3" width="9%">Addr'</td><td align="center" bgcolor="#d3d3d3" width="65%"> If use below Formula, You'll Get Result as Right</td><td align="center" bgcolor="#d3d3d3" width="15%">Result</td><td align="center" bgcolor="#d3d3d3">Formula's</td></tr><tr height="20"><td align="center">1</td><td align="center" bgcolor="#ccffcc">H31</td><td bgcolor="#ccffcc">=TODAY()</td><td align="right">2008-10-14</td><td align="right"><form name="PrNames1"><input onclick='window.clipboardData.setData("Text","=TODAY()");' value="Do Copy" name="MyNames1" type="button"></form></td></tr><tr height="20"><td align="center">2</td><td align="center" bgcolor="#ccffcc">K31</td><td bgcolor="#ccffcc">=DATE(YEAR(H31), MONTH(H31), 1)</td><td align="right">2008-10-01</td><td align="right"><form name="PrNames2"><input onclick='window.clipboardData.setData("Text","=DATE(YEAR(H31), MONTH(H31), 1)");' value="Do Copy" name="MyNames2" type="button"></form></td></tr><tr height="20"><td align="center">3</td><td align="center" bgcolor="#ccffcc">E33</td><td bgcolor="#ccffcc">=IF(MONTH($K$31+(ROW()-33)*7+COLUMN()-4-WEEKDAY($K$31))=MONTH($K$31),$K$31+(ROW()-33)*7+COLUMN()-4-WEEKDAY($K$31),TEXT($K$31+(ROW()-33)*7+COLUMN()-4-WEEKDAY($K$31),"(dd)"))</td><td align="left">(28)</td><td align="right"><form name="PrNames3"><input onclick='window.clipboardData.setData("Text","=IF(MONTH($K$31+(ROW()-33)*7+COLUMN()-4-WEEKDAY($K$31))=MONTH($K$31),$K$31+(ROW()-33)*7+COLUMN()-4-WEEKDAY($K$31),TEXT($K$31+(ROW()-33)*7+COLUMN()-4-WEEKDAY($K$31),\"(dd)\"))");' value="Do Copy" name="MyNames3" type="button"></form></td></tr><tr height="20"><td align="center">4</td><td align="center" bgcolor=""> </td><td>E33 His Formula Used This Cell -> E33:K38</td><td align="left"> </td><td> </td></tr><tr height="24"><td colspan="5">
How about this suggest?
</td></tr></tbody></table>

 

[menor59]

Thank you That did it...I just noticed the Column -4

 

[mattrx731]

Thanks to all for the different suggestions on this thread.

Appreciate it,
Matt