Excel weekday formula
Need the day of the week next to a date, today’s date, or someone’s age in years? TEXT, WEEKDAY, TODAY, and DATEDIF cover those jobs. Walk the static examples below, then practice turning a date column into weekday labels.
Weekday and date formulas at a glance
Four related formulas cover most date-and-weekday jobs. TEXT formats a date as a weekday name. WEEKDAY returns a day number. TODAY returns today’s date. DATEDIF measures age or elapsed years. The worked example below starts with TEXT; the sections after cover WEEKDAY, TODAY, and DATEDIF.
Formats a date (or number) as text. For weekdays in English Excel, use "ddd" (Mon) or "dddd" (Monday). In Japanese Excel, "aaa" returns 月・火・水… and "aaaa" returns the full name.
Returns 1–7 for the day of the week. return_type 2 makes Monday = 1 and Sunday = 7 — handy with CHOOSE or INDEX when you need custom weekday labels.
TODAY() has no arguments and refreshes every time the workbook recalculates. DATEDIF with unit "Y" returns whole years between two dates — the usual pattern for age.
TEXT example: weekday from a date
This TEXT example formats each date as a three-letter English weekday.
| Date | Weekday |
|---|---|
| 2026-10-14 | Wed |
| 2026-10-12 | Mon |
| 2026-10-17 | Sat |
TEXT(A2, "ddd") returns Wed for 2026-10-14. Use "dddd" for Wednesday. In Japanese Excel, TEXT(A2, "aaa") returns 水 instead — see the Japanese note in the sections below.
WEEKDAY example: day number, then a custom label
WEEKDAY returns 1–7. With return_type 2, Monday is 1 and Sunday is 7. Pair it with CHOOSE when you want your own weekday names.
| Date | WEEKDAY(…, 2) | Label |
|---|---|---|
| 2026-10-14 | 3 | Wed |
| 2026-10-12 | 1 | Mon |
| 2026-10-17 | 6 | Sat |
Map Monday=1 … Sunday=7 to English abbreviations
TODAY example: insert today’s date automatically
TODAY() has no arguments. It returns the current date and updates whenever the workbook recalculates — useful for “as of today” headers and age formulas.
Today’s date in a cell
DATEDIF example: calculate age in years
DATEDIF(start_date, end_date, "Y") returns whole years between two dates. Pair the birth date with TODAY() for a live age.
| Birth date | As of | Age (years) |
|---|---|---|
| 1990-05-01 | 2026-10-14 | 36 |
Age in completed years
Calendar layouts (月間カレンダー) are a separate topic from weekday formulas. This guide stays on TEXT / WEEKDAY / TODAY / DATEDIF; a dedicated calendar page may follow later.
Japanese Excel: TEXT with aaa for 曜日
In Japanese Excel, =TEXT(A2, "aaa") returns 月・火・水・木・金・土・日. Use "aaaa" for the full weekday name. Many non-Japanese engines treat "aaa" like English "ddd", so WEEKDAY + CHOOSE is the portable fallback — and what the practice sheet grades.
Japanese Excel weekday (short)
4 weekday and date gotchas
1. Format codes are locale-specific
Japanese Excel uses "aaa" for short weekdays (月・火・水). English Excel uses "ddd". In Google Sheets and many engines, "aaa" may not produce Japanese characters — use WEEKDAY + CHOOSE when you need a portable formula.
2. WEEKDAY’s second argument changes numbering
Omit return_type and Sunday is 1. Pass 2 and Monday is 1. Mixing the two silently shifts every CHOOSE or INDEX map by one day.
3. Dates are serial numbers
A cell that looks like 2026-10-14 may still be text. WEEKDAY and TEXT need a real date. Wrap text dates with DATEVALUE, or enter them as dates.
4. TODAY recalculates; hardcoded dates do not
TODAY() updates whenever the sheet recalculates — useful for “today’s date” and age formulas. A typed date stays fixed until you change it.
Excel vs Google Sheets
Microsoft Excel (365 / Desktop)
Desktop Excel and Microsoft 365 support TEXT, WEEKDAY, TODAY, and DATEDIF. Japanese Excel’s "aaa" / "aaaa" weekday codes are the usual way to show 曜日 automatically next to a date.
Google Sheets
Google Sheets supports the same function names. Prefer WEEKDAY + CHOOSE (or TEXT with "ddd") when you need weekday labels that stay consistent across locales.
Weekday exercise for this guide
Weekday exercise
Turn a column of dates into Mon–Sun with TEXT or WEEKDAY+CHOOSE. The formula has to follow A2.
FAQ
What is the Excel weekday formula?
=TEXT(A2, "ddd") returns Mon–Sun. =WEEKDAY(A2, 2) returns 1–7 (Monday–Sunday). Combine WEEKDAY with CHOOSE for custom labels.
How do I show the day of the week in Excel?
Use TEXT with "ddd" or "dddd", or WEEKDAY plus CHOOSE/INDEX. In Japanese Excel, TEXT with "aaa" shows 月・火・水…
How do I insert today’s date automatically?
Enter =TODAY() in a cell. It updates whenever the workbook recalculates. For a fixed stamp that never changes, type the date manually or use Ctrl+; in Excel.
How do I calculate age in Excel?
=DATEDIF(birth_date, TODAY(), "Y") returns completed years. Change "Y" to "M" or "D" for months or days.
What does WEEKDAY return_type 2 mean?
Monday is 1 and Sunday is 7. Without the second argument, Sunday is 1. Pick one numbering scheme and keep CHOOSE lists in the same order.
Why does TEXT return a weird weekday?
The cell may be text, not a date. Convert with DATEVALUE, or enter a real date. Also check the format code matches your Excel language.
Can I use these formulas in Google Sheets?
Yes — TEXT, WEEKDAY, TODAY, and DATEDIF (or YEAR/MONTH differences) work under the same names. Prefer WEEKDAY + CHOOSE when weekday labels must stay consistent across locales.
TEXT aaa vs ddd — which should I use?
"aaa" is the Japanese Excel short-weekday code. "ddd" is the English one. Use aaa on Japanese Excel for 月・火・水; use ddd or WEEKDAY+CHOOSE when the engine is English-locale.