Small 7 x 5 monthly calendar Excel 2010

[JJayne]

Hi all, I havent been able to figuire this one out so looking for some help. I want to create a simple Mon - Sun 7 cell across by 5 cells down month only calendar based on month value (January 2015)shown in cell A1. I would like to change the month in A1 and have the dates automatically change as well.

 

[Drrellik]

I am Sure VBA would be the cleanest way to go but it is also the most challenging for many of us, including me at this point. Would you be able to list the entire year and all the days of the week in a separate table and then use index-match to lookup or even vlookup to fill in the cells based on your A1 entry.

 

[JJayne]

I am Sure VBA would be the cleanest way to go but it is also the most challenging for many of us, including me at this point. Would you be able to list the entire year and all the days of the week in a separate table and then use index-match to lookup or even vlookup to fill in the cells based on your A1 entry.

Hmm still too noob to know how to do that as well im afraid, Ill try and study it up. Thanks for your help!

 

[igold]

This is not my code, I got it from a John Walkenbach book, but it works great...

Highlight your entire 7x5 grid and put this formula in the formula bar. You must hit Control-Shift-Enter to enter the formula. If the formula is not surrounded by these { }, you have not entered it correctly. You should format your grid as either d or dd (Custom Format). You can then add the name of the days (Mon, Tue, etc and the Month) as you see fit. The value in A1 should be something like 8/2015 for August...

=IF(MONTH(DATE(YEAR(A1),MONTH(A1),1))<>MONTH(DATE(YEAR(A1),MONTH(A1),1)-(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)," ",DATE(YEAR(A1),MONTH(A1),1)-(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)

HTH

igold

 

[Drrellik]

That is sweet.

 

[Drrellik]

I was playing around with this and found that in some months with 31 days if the first was on a Friday or Saturday then you will need a 6th row to accommodate the 31. so make your grid a 7x6

 

[JJayne]

Wow that works perfectly! Thank You!

This is not my code, I got it from a John Walkenbach book, but it works great...

Highlight your entire 7x5 grid and put this formula in the formula bar. You must hit Control-Shift-Enter to enter the formula. If the formula is not surrounded by these { }, you have not entered it correctly. You should format your grid as either d or dd (Custom Format). You can then add the name of the days (Mon, Tue, etc and the Month) as you see fit. The value in A1 should be something like 8/2015 for August...

=IF(MONTH(DATE(YEAR(A1),MONTH(A1),1))<>MONTH(DATE(YEAR(A1),MONTH(A1),1)-(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)," ",DATE(YEAR(A1),MONTH(A1),1)-(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)

HTH

igold

 

[igold]

Great. I am glad I could help. Thanks for the feedback.