DMW logo
tel 01732 833085
Tonbridge · Kent · UK · UK

How to Extract the Day From a Date in Excel

How to create a formula for extracting the day of the week from a date.

Last updated on 2021-02-19 by David Wallis.


This spreadsheets gives examples of three ways we might represent the dates listed in Column A:

Day of week spreadsheet

The contents of the cells in Columns B, C and D are the results of formulas in those cells, as explained below.

I’m writing this article on a computer that has the United Kingdom as its regional setting. Its default format for dates is dd/mm/yyyy.

Column B — The Weekday Number

This is the formula in Cell B2:


Noting that Excel’s WEEKDAY function returns a number representing the location of a day in a seven-day week, you observe that Excel’s Sunday appears as Day 1, as the first day of the week.

That’s because the formula omits to include a value for the arbitrary second argument of the WEEKDAY function. By assigning a numeric value to that argument from this list, we can instruct WEEKDAY to start the week on any day we like to choose:

WEEKDAY second argument

I prefer to have the week start on a Monday, so the formula in Cell B2 becomes this:


Note that your choice of a second argument has a bearing on the construction of the formulas in Columns C and D.

Columns C and D

You can use Excel’s TEXT function to spell out the name of a weekday. The TEXT function has this makeup:

=TEXT(Value_to_format, "Format_to_apply")

This the formula in Cell C2 of the above spreadsheet:


And this, in Cell D2:


Three ds for the first three letters of a day's name; four, to spell out the name in full.

Starting a Week on a Monday

Now for a Monday start to the week:

Day of week spreadsheet

In Cell B2 the formula is this:


This the formula in Cell C2:


And this, in Cell D2:


The +2 compensates the TEXT function for the use of the second argument in the WEEKDAY.

To help understanding of what’s going on, I recommend that you test your formula on today’s date. That’ll make it easy to vet the formula.

Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

If you’d like an invoice to account for your donation, let me know how much you’re donating —


Thanks, in anticipation.

SSL Cerification

“I have always been delighted at the prospect of a new day, a fresh try, one more start, with perhaps a bit of magic waiting somewhere behind the morning.”

J. B. Priestley