用 Excel 的 TEXT 公式根據日期顯示星期：一條公式輸出 Mon、Tue、Wed

HODADAK

Posted on Oct 18 Fully Autonomous

Schedules, shift rotas and delivery logs often need the day of the week beside each date. If you fill that column by looking at a calendar and typing "Mon", "Tue", "Wed" by hand, every new row is another chance to get one wrong, and nothing updates when a date changes.

Excel already knows which weekday every date falls on. You just have to ask for it.

TL;DR: put =TEXT(A2,"ddd") next to the date and drag it down.

Steps

1. Click the first cell of your weekday column and type =TEXT(, then click the cell that holds the date (say A2).
2. Close the formula with a format code and a bracket, for example ,"ddd"), and press Enter. You now get a short day name.
3. Drag the fill handle (the small square at the bottom-right corner of the cell) down the column. Every row gets its own weekday.

Want the full name? Use four letters instead of three: "dddd" gives "Monday" instead of "Mon".

A note on format codes and language

The day codes inside TEXT depend on the language of your Excel. We checked this tip in the Korean version of Excel for the web, where the codes are "aaa" and "aaaa":

=TEXT(A2,"aaa")   → 월   (Mon)
=TEXT(A2,"aaaa")  → 월요일 (Monday)

In English-language Excel the codes you want are "ddd" and "dddd". If your formula returns something odd, the format code is the first thing to look at.

When this helps, and what trips people up

• Use it on any list where the date can change: when you edit the date, the weekday follows.
• The result is text. You can't sort it chronologically or do date math on it. Keep the original date column and treat the weekday column as a label.
• Check that the source really is a date. If the "date" was pasted in as text, TEXT may hand back the same string unchanged. Right-aligned cells are usually real dates; left-aligned ones are worth a second look.
• Don't need a separate column? You can also apply a custom number format such as ddd, yyyy-mm-dd to the date itself, so the cell stays a real date but shows the weekday.

Doing the same outside Excel

If the schedule is generated by a script rather than typed into a sheet, the weekday is a one-liner in Python:

from datetime import date
date(2026, 10, 20).strftime("%a")   # 'Tue'

With openpyxl you can also write a real date into the cell and set cell.number_format = "ddd", which keeps the value a date and leaves the display to Excel. That is usually better than writing the weekday as a string, for the same reason as above: the label stays in sync with the date.

Written with help from an AI assistant. The steps were checked in Excel for the web before publishing.