With January 1, 2000 in cell B5, the TEXT function returns the text string “Saturday”. For all examples, keep in mind that Excel dates are large serial numbers, displayed as dates with number formatting.
Day name with custom number format
To display a date using only the day name, you don’t need a formula; you can just use a custom number format. Select the date, and use the shortcut Control + 1 to open Format cells. Then select Number > Custom, and enter one of these custom formats: Excel will display only the day name, but it will leave the date intact. If you want to display both the date and the day name in different columns, one option is to use a formula to pick up a date from another cell, and change the number format to show only the day name. For example, in the worksheet shown, cell F5 contains the date January 1, 2000. The formula in G5, copied down, is: Cells G5 and G6 have the number format “dddd” applied, and cells in G7:G9 have the number format “ddd” applied.
Day name with TEXT function
To convert a date to a text value like “Saturday”, you can use the TEXT function. The TEXT function is a general function that can be used to convert numbers of all kinds into text values with formatting, including date formats. For example, with the date January 1, 2000 in cell A1, you can use TEXT like this: In the worksheet shown, the goal is to display the day name only, so we use a custom number format like “ddd” or “dddd”: Note: The TEXT function converts a date to a text value using the supplied number format. The date is lost in the conversion and only the text for the day name remains.
Day name with CHOOSE function
For maximum flexibility, you can create your own day names with the CHOOSE function. CHOOSE is a general-purpose function for returning a value based on a numeric index. For example, you can use CHOOSE to return one of three colors with a number like this: In this example, the goal is to return a day name from a date, so we need to configure CHOOSE to select one of seven-day names. For example, the formula below would return “Wed” based on a numeric index of 4: The challenge in this case is to get the right index for a date and for that we need the WEEKDAY function. For any given date, WEEKDAY returns a number between 1-7, which corresponds to the day of the week. By default, WEEKDAY returns 1 for Sunday, 2 for Monday, 3 for Tuesday, etc. In the worksheet shown, the formula in C12 is: WEEKDAY returns a number between 1-7, and CHOOSE will use this number to select the corresponding value in the list. Since the date in B12 is January 1, 2000, WEEKDAY returns 7, and CHOOSE returns “Sat”. CHOOSE is more work to set up, but it is also more flexible, since it allows you to convert a date to a day name using any values you want. For example, you can use custom abbreviations or even abbreviations in a different language. In cell C15, CHOOSE is set up to use one letter abbreviations that correspond to Spanish day names:
Empty cells
If you use the formulas above on an empty cell, you’ll get “Saturday” as the result. This happens because an empty cell is evaluated as zero, and zero in the Excel date system is the date “0-Jan-1900”, which is a Saturday. To work around this issue, you can use the IF function to return an empty string ("") for empty cells. For example, if cell A1 may or may not contain a date, you can use IF like this: The literal translation of this formula is: If A1 is not empty, return the TEXT formula, otherwise return an empty string ("").
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
