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’s
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’s TEXT() function syntax
= TEXT (Value to change, “format to apply”)
Value to changecan 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.
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:
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.