How to Calculate the Day of the Week in Excel

Last Updated: April 10, 2019

You've just entered a bunch of dates into your Excel spreadsheet, but what you really want to see is what day of week those dates happen on. Fortunately, Excel makes it easy to calculate the day of week with a simple formula. With a bit of handiwork, you can get the abbreviated or full weekday name. You'll just have to know the appropriate excel shortcut: =TEXT((A1), "ddd")

Steps

1
Enter a date reference in a cell. For this example, we'll use the date "11/7/2012." In A1, enter that date.
2
Calculate the abbreviated weekday name. In cell B1, enter =TEXT((A1), "ddd") into the cell or formula field.
- The "ddd" setting tells Excel to use the first three letters of the weekday name. In this example, "ddd" becomes "Wed".
Advertisement
3
Calculate the full weekday name. In cell C1, enter =TEXT((A1), "dddd").
- This will calculate the full weekday name.
- To add additional date info, use the following conventions, in any order:
  - Time: hh:mm:ss will give you the full time. You can also enter any part of that for more abbreviated time displays.
  - Day of week: as described above, ddd gives you the abbreviated day name, and dddd gives you the full day name.
  - Date: dd will give you the date with a leading zero for the 1st through the 9th. A single d will drop the leading zero.
  - Month: mmm will give you the abbreviated month, and mmmm will give you the month spelled out.
  - Year: For just the decade, use yy. For the complete year, use yyyy.
- For example, to have field A1 (as above) read as "Wed, 7 Nov., 2012" you would enter "=TEXT((A1), "ddd, d mmm., yyyy"). Make sure you include the quotes, and that your parentheses are balanced (as many open ones as closed ones).

Community Q&A

Question

What is the calculation of 30 to 31 weeks?

Community Answer

Let's say you have a date in cell A1: In B1 type: =weekday(A1;2). To find which day of the month type: =day(A1). For day of the year type : =day360(A1). To find out the week of the year type: =weeknum(A1;2).
Question

What code would I use for the column to automatically do this all the way down when dates are entered into another column?

Community Answer

Simply replicate the formula down the column if using the "A1" cell as started. Otherwise, alter the formula accordingly.