CODEWITHSUNDEEP
exceldateexcel-formulacalendarorganization
Alex
Alex

Reputation: 170

Excel: Formula to fill dates in a Weekly Calendar

I found a template for an Excel monthly calendar and it works great for me. Each month is in its own sheet (Tab) and there is a formula that pre-populates the days of the month.

=DaysAndWeeks+DATE(CalendarYear,1,1)-WEEKDAY(DATE(CalendarYear,1,1),(WeekStart="Monday")+1)+1

And it looks like this: (I guess I can't post images yet, but there is a link..) Monthly Calendar Image

The formula is used on the day of the month. In the image example for the month of January, the formula is displaying: 31, 1, 2, 3, 4, 5, 6, etc....

What I am trying to do is make a new Excel workbook with the Weeks of the Year. It would follow the same format, one week per sheet (tab), so there would be 52 sheets + 1 in the beginning I will call template, that I can make changes to and copy to the week tabs. So I don't have to Change each one individually.

So I am looking for a formula that will fill in the DayOfWeek month/Day at the top of my week "table". This is what I am using at the moment for my weekly calendar: (again, I can't post images, but its in the link) Weekly Calendar Image

The formula I am looking for will span the whole workbook (same as it does in my monthly calendar) and pre-populate the dates in each sheet.

Any Excel Wizards out there that can wave their wands?? =)

Thanks for any help. And if you guys want any of the workbooks to use, just let me know, I can share them. (btw, I use Excel 2016)

Upvotes: 0

Views: 4473

Answers (1)

Jason K
Jason K

Reputation: 1

In your post, it looks like the monthly calendar formula is referencing the cells under the "calendar settings." For the weekly spreadsheet, you can use something like ==IFERROR(UPPER(TEXT(DATE(ReportYear,MonthNumber,ReportDay),"MM D")),"") which I pulled from https://templates.office.com/en-us/Daily-work-schedule-TM02780252 and formatted it to use "MM D".

I would appreciate you sharing the monthly calendar and I'll certainly give the weekly calendar a go as well if you wouldn't mind sharing.

Upvotes: 0

Related Questions