Day of the week from date in Excel

Image of calendar

Get the day of the week from a date in Excel

To get the day of the week from a date in Excel, we can use Excel’sTEXT() function.

Examples of working with days of the week and differences in dates in Excel

The TEXT() function lets you change the way a numeric value appears by applying formatting to it. It’s useful where you want to make numbers more readable, or to combine numbers with text or symbols. There are various format codes available:

  • "0.0%" to return a percentage;
  • "MM/DD/YYYY" to return a date in the format month/day/year, 12/25/2019;
  • "h:mm AM/PM" to display time in the format ’12:30 PM’.
Excel Format Cells dialog box
The range of text formats can be found in Excel’s Format Cells dialog box.
Excel’s TEXT() function syntax

= TEXT (Value to change, “format to apply”)

  • Value to change can be a value entered directly into the function, or a cell reference
  • Format to apply: note that the format to apply needs to be within speech marks, e.g. "dddd".
Get the day of the week syntax

= TEXT (“01/01/2019”, “dddd”)

= TEXT (R3, “ddd”)

Dates can be fed to the function via cell references, e.g. cell R3 as below:

Excel's TEXT() function examples

NOTE: as we’ve seen on the previous page with WEEKDAY(), if a date is entered directly into the function, it has to be enclosed in speech marks; if a date is entered into the TEXT() function in an invalid format (without speech marks), a value is returned NOT an error. So, test your formula with a few values for which you know what the correct results should be.

Pages: 1 2