In Excel: start from File ▸ New and search the Calendar templates, or build one from formulas: put =DATE($B$1,$B$2,1) in the first cell and add 1 across the row.
For a ready-made one: File ▸ New, search "calendar", pick a template and change the year cell it exposes.
For a reusable grid: put the year and the month in two named input cells.
Compute the first visible cell with =DATE(year,month,1)-WEEKDAY(DATE(year,month,1),2)+1 so the grid starts on a Monday.
Fill the rest of the row with +1 from the cell to its left, and start each new row from the last cell of the previous one.
Apply the custom number format d so each cell shows only the day number.
Add conditional formatting to grey out days from the neighbouring months and to highlight =A4=TODAY().
For anything you will filter or report on, use one date per row instead of a grid.
What this does
Three approaches, in increasing effort. Microsoft ships ready-made calendar templates under File ▸ New — search "calendar" and pick a yearly, monthly or academic layout. A formula-driven calendar is better when it has to be reusable: one cell holds the year, another the month, and every date cell derives from those, so changing the month redraws the grid. And a simple date list — one date per row down a column — is what you actually want for a schedule or a booking sheet, since it filters, sorts and pivots, which a grid layout does not. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “choose date from calendar excel”. Start on a copy or a tiny sample, keep the affected cells visible, and compare the result with the tool above before you touch the real workbook. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. The point is a workflow that saves repeating the same clicks every week, but the practical win is that someone else can open the file and understand what happened without asking you.
A worked example
A reusable monthly grid: B1 = 2026, B2 = 8. In A4 put =DATE($B$1,$B$2,1)-WEEKDAY(DATE($B$1,$B$2,1),2)+1 to land on the Monday of the first week, then B4 = A4+1 filled across to G4, and A5 = G4+1 to start the next row. Format the range with the custom number format d so only the day number shows, and add a conditional-formatting rule =MONTH(A4)<>$B$2 with grey text to fade the leading and trailing days. Changing B2 to 9 redraws the whole month. A calendar built from formulas is reusable for every month and every year, while a typed one is thrown away in four weeks — and knowing when to use a date list instead saves rebuilding the whole thing once someone asks for a report. If there is any chance you will reuse this, drop it into a small template tab right now: a labelled input area on the left and the formula beside it, checked once against the tool above. Next time the same question comes up, the answer is a single paste away instead of a rebuild from memory.
In Google Sheets
Everything above works in Google Sheets too. Excel and Sheets share the formula syntax used here; only the surrounding menus are arranged differently. That portability is deliberate — learn it once and it follows you between the two tools and across Windows and Mac. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top, the tool proves it, and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. If you take one thing from this page on “choose date from calendar excel”, make it the habit rather than the keystrokes: set the problem up with labelled inputs, reference those cells, and let Excel do the recomputing. Bookmark the page for the syntax, but do the example once in a blank sheet and check it against the tool above — five minutes of hands-on practice fixes the method in memory far better than re-reading, and it surfaces the small snags while they are still harmless. After that the technique is genuinely yours: faster than searching for it again, and reliable enough to drop into work that other people depend on.
Common mistakes
- Typing the dates by hand, which makes the calendar single-use and guarantees a wrong weekday somewhere.
- Using
TODAY()inside the grid formulas, so the calendar shifts every day instead of staying on the chosen month. - Building a grid when the real need is a schedule — a grid cannot be filtered, sorted or pivoted.
- Forgetting
WEEKDAY's second argument; the default counts Sunday as day 1, which shifts a Monday-start calendar by one. - Formatting the cells as text so the dates stop being real date serials.
Frequently asked questions
Does Excel have calendar templates?
Yes — File ▸ New and search "calendar". Microsoft ships yearly, monthly, academic and planner layouts that you can edit like any workbook.
How do I make a calendar that updates when I change the month?
Put the year and month in two cells and derive every date from them, starting with =DATE(year,month,1)-WEEKDAY(DATE(year,month,1),2)+1. Changing the month cell then redraws the grid.
How do I show only the day number?
Select the date cells, press Ctrl+1 ▸ Number ▸ Custom and enter d as the format code. The cell still holds a full date, which is what the conditional formatting needs.
How do I highlight today?
Add a conditional-formatting rule with the formula =A4=TODAY() over the calendar range and give it a fill colour.