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. 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. 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. 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).
  4. Advertisement

Community Q&A

  • Question
    What is the calculation of 30 to 31 weeks?
    Community Answer
    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
    Community Answer
    Simply replicate the formula down the column if using the "A1" cell as started. Otherwise, alter the formula accordingly.
Advertisement

Things You'll Need

  • Microsoft office package(excel sheet)
  • Desktop or phone using excel
  • Microsoft excel knowledge

About This Article

wikiHow is a “wiki,” similar to Wikipedia, which means that many of our articles are co-written by multiple authors. To create this article, 17 people, some anonymous, worked to edit and improve it over time. This article has been viewed 554,776 times.
How helpful is this?
Co-authors: 17
Updated: April 10, 2019
Views: 554,776
Categories: Microsoft Excel
Advertisement