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:
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:
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:
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:
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.