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 2024-05-15.

Introduction

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

Day of week Excel 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:

Excel WEEKDAY function 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:

Excel spreadsheet of day of week

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) —