Yearly Planner in Excel

This page contains instructions for creating a planner for a single year in Excel. The resulting Excel workbook will contain a row per day of the year. The workbook should have the following columns.

1. Date
2. Weekday
3. Week number
4. In
5. Lunch
6. Out
7. Comment

Header

To lock the first row to create a header select the following Excel command

View > Freeze Pane > Freeze top row

Date column

Use the following formula to create a column of dates for the whole year.

=DATE(2024, 1, ROW(A2))

Press Ctrl and drag the formula down so that there is one row per day of the year. This produces a colum that contains each date of the year.

Weekday column

Use the following formulat to create a column containing the weekday names of the dates in the date column.

=TEXT(A2, "ddd")

Week number column

Use the following formula to create a column containing the week numbers of the dates in the date column.

=ISOWEEKNUM(A2)

Remaining columns

Leave the remaining columns blank.