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

Attached Files

CALENDER.xlsx (10.3 KB, 23 views)

Before going any further, it would be best to get rid of the merged cells. Using them is begging for trouble

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

OK
I have modified the spreadsheet with merged cells

Thanks

Attached Files

CALENDER.xlsx (9.7 KB, 21 views)

Hi

I assume you use US weekday counting, i.e. Sunday is the 1st day of the week?
When this is the case, then:
1. Add an additional row to your calendary so you don't lose some month's endings;
2. Extend your months list in column O (and fill all gaps in it, so you have all months from January to December listed (Range O2:O13);
3. Copy this formula into leftmost upper cell of your calendary (D9):
=IF(MONTH(DATE($L$12,MATCH($L$9,$O$2:$O$13,0),1)-(WEEKDAY(DATE($L$12,MATCH($L$9,$O$2:$O$13,0),1))+0 ) + (COLUMN()-COLUMN($C:$C))+7*(ROW()-ROW($9:$9)))=MATCH($L$9,$O$2:$O$13,0),DATE($L$12,M ATCH($L$9,$O$2:$O$13,0),1)-(WEEKDAY(DATE($L$12,MATCH($L$9,$O$2:$O$13,0),1))+0 ) + (COLUMN()-COLUMN($C:$C))+7*(ROW()-ROW($9:$9)),"")

(Check the formula for semicolons instaed of comma for case I missed some replacements there!)
4. Copy the formula in D9 into entire calendary table (D9:J14). Format calendary cells as custom "d".