How To Make A Calendar In Excel Using Formula

[rich_cirillo]

Hi

What formulas do i need/use to get the day numbers into the calender for each year/month.When the month and/or year are changed the days number changes

Thanks

Rich - excel 2010

[Excel Fox]

I've modified your file a bit, to take out merged cells (try to avoid them for various reasons I'm not going over here).

I've used this formula

=IF(MONTH(DATEVALUE(F4&" "&D4))<>MONTH(DATEVALUE(F4&" "&D4)-(WEEKDAY(DATEVALUE(F4&" "&D4))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",DATEVALUE(F4&" "&D4)-(WEEKDAY(DATEVALUE(F4&" "&D4))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1) as an array

[rich_cirillo]

Thanks....looks great

Is it possible to just display the number instead of the full date....eg..instead of the 10/5/2013 it just displays the day number 10 or 15 instead of 15/7/2013

Thanks again

[msiyab]

Where's August?

[rich_cirillo]

Hi

I have now added the forgotten August month

Thanks

[Excel Fox]

Either format the cells to just DD format, OR, use this

=IF(MONTH(DATEVALUE(F4&" "&D4))<>MONTH(DATEVALUE(F4&" "&D4)-(WEEKDAY(DATEVALUE(F4&" "&D4))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",DAY(DATEVALUE(F4&" "&D4)-(WEEKDAY(DATEVALUE(F4&" "&D4))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1))

[paul_pearson]

Hi Excelfox

Thanks to you

Works well

Paul