DMW logo
(t) +44 (0)1732 833085
(e) david@consultdmw.com

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.


Introduction

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:

=WEEKDAY(A2)

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:

=WEEKDAY(A2,2)

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:

=TEXT(WEEKDAY(B2),"ddd")

And this, in Cell D2:

=TEXT(WEEKDAY(B2),"dddd")

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:

=WEEKDAY(A2,2)

This the formula in Cell C2:

=TEXT(WEEKDAY(B2,2)+2,"ddd")

And this, in Cell D2:

=TEXT(WEEKDAY(B2,2)+2,"dddd")

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 —

Invoice

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