mattrx731
Board Regular
- Joined
- Feb 10, 2008
- Messages
- 181
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.
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.
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
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