It’s 3:12 PM on Tuesday, April 23rd. Your team lead just forwarded an email: ‘Please share the May 2025 calendar layout by EOD — needs public holidays flagged and Monday-start grid.’ You open Excel. A blank sheet. No template. No idea why your =DATE(2025,5,1) keeps landing on a Sunday instead of Monday.
The Setup
You’re handed this raw data block in Sheet1, pasted from HR’s internal portal — no headers, inconsistent spacing, and two extra blank rows at the top:
| A1 | B1 | C1 | D1 |
|---|---|---|---|
| May 2025 | US Federal Holidays | Date | Notes |
| Memorial Day | 2025-05-26 | Observed | |
| Juneteenth | 2025-06-19 | Not in May — ignore | |
| Mother's Day | 2025-05-11 | Unofficial | |
| Cinco de Mayo | 2025-05-05 | Unofficial | |
| Flag Day | 2025-06-14 | Not in May — ignore | |
| Graduation Week | 2025-05-19 to 2025-05-23 | Multi-day event |
The Challenge
You need a clean, Monday-start May 2025 grid (7 columns × 6 rows), where each cell contains only the day number (1–31), with weekends shaded, holidays bolded and colored, and no blank cells misaligned. The trap? Excel treats May 1, 2025 as a Thursday — but your org requires Monday-first layout. If you just type 1–31 across rows, you’ll get Sunday under column G and June 1 spilling into row 6, column A.
Worse: =WEEKDAY(DATE(2025,5,1)) returns 5 (Thursday). But Excel’s default WEEKDAY() starts Sunday=1. So if you use =WEEKDAY(DATE(2025,5,1),2), it returns 4 — meaning Thursday is day 4 of a Monday-start week. That’s the key. Most people skip this second argument and spend 20 minutes debugging offsets.
Walking Through It
Start fresh in Sheet2. In cell A1, type Mon. In B1, type Tue. Fill right through G1.
In A2, enter this formula:=DATE(2025,5,1)-WEEKDAY(DATE(2025,5,1),2)+1
This gives you April 28, 2025 — the Monday before May 1.
Now drag that formula right to G2. Each cell increments by 1. Then select A2:G2 and drag down to row 7. You now have 6 full weeks, starting April 28 and ending June 1.
Before: A2 contains 44315 (serial number for April 28, 2025). Not helpful.
| A2 | B2 | C2 | D2 | E2 | F2 | G2 |
|---|---|---|---|---|---|---|
| 44315 | 44316 | 44317 | 44318 | 44319 | 44320 | 44321 |
After: Apply custom number format d to A2:G7. Now you see day numbers only — 28, 29, 30, 1, 2, 3, 4.
| A2 | B2 | C2 | D2 | E2 | F2 | G2 |
|---|---|---|---|---|---|---|
| 28 | 29 | 30 | 1 | 2 | 3 | 4 |
Select A2:G7 → Home tab → Conditional Formatting → New Rule → Use a formula. For weekends: =OR(WEEKDAY(A2,2)=6,WEEKDAY(A2,2)=7). Set fill to light gray (#e6e6e6).
Now pull in holidays. In Sheet1, filter Column C for dates between 2025-05-01 and 2025-05-31. Copy those dates (C3, C5, C6). In Sheet2, select A2:G7 → Find & Select → Go To Special → Constants → Numbers. Press Ctrl+H. Replace “26” with “26”, but don’t do that manually — use a formula in a helper column first. Instead: In cell I1, type =IF(COUNTIF(Sheet1!$C$3:$C$8,A2),"★",""), then copy across I1:O7. Paste values back over A2:G7 using Paste Special → Values + Multiply (Alt+E+S+V then Alt+E+S+M).
The Result
Here’s what A2:G7 looks like after all steps — clean, Monday-aligned, holidays marked with ★, weekends shaded:
| Mon | Tue | Wed | Thu | Fri | Sat | Sun |
|---|---|---|---|---|---|---|
| 28 | 29 | 30 | 1 | 2 | 3 | 4 |
| 5 | 6 | 7 | 8 | 9 | 10 | 11★ |
| 12 | 13 | 14 | 15 | 16 | 17 | 18 |
| 19 | 20 | 21 | 22 | 23 | 24 | 25 |
| 26★ | 27 | 28 | 29 | 30 | 31 | 1 |
| 2 | 3 | 4 | 5 | 6 | 7 | 8 |
What Could Go Wrong
Mistake 1: Using WEEKDAY without the second argument. You’ll get Sunday=1, so =DATE(2025,5,1)-WEEKDAY(DATE(2025,5,1))+1 gives April 27 (a Sunday), not April 28. Your whole grid shifts left by one column. Fix: Always use WEEKDAY(date,2).
Mistake 2: Applying conditional formatting to the entire range before adding day numbers. Excel formats serial numbers — not visible day numbers — so weekend shading appears on wrong cells. You’ll see gray on 44320 and 44321 instead of 6 and 7. Fix: Format *after* applying custom number format d.
Mistake 3: Manually typing 1–31 across rows. This breaks when May starts mid-week. You’ll end up with 31 in column E, then blanks in F/G, and June 1 spills into row 6, column A — breaking the 6-row grid. Fix: Always anchor to =DATE(2025,5,1) and calculate backward.
Next step: Copy the final A2:G7 block. Right-click → Paste Special → Picture (ALT+H+V+P). Paste into Outlook or Teams as a clean image — no formulas, no formatting surprises.
