How to get day name from the date
Do you want to learn how to get the day name from a date? Reading this post will teach you how to know the day name of a date, useful if you’re carefully planning your time or if you want to know if a certain date lands on a weekend or on a workday.
Figure 1. Get day name from date example datasheet
How does this formula to get the day name from a date works?
Excels uses numbers to read dates and times, these numbers start at 1, which represents January 1 – 1900 and end at 2958465, which represents December 31 – 9999. The formula just tells Excel to display which day is it on a certain date. As a fun fact, due to compatibility issues with other spreadsheet programs when Excel was created, the year 1900 is treated as a leap year and Excel won’t display days of the week correctly for any date before March 1, 1900.
As it was shown before in the image above, there are different ways to display the days of the week, and each way has its own formula.
But to simply display the day name from a date, you can just use a personalized format to show the day of the week on any desired cell, just select it, then go to the format cells options and pick one of the following custom formats:
“ddd” = “Mon”
“dddd” = “Monday”
If you want Excel to get the abbreviated day name from a date, you can order it to do so using the following formula, just change the date part with the cell that holds the desired date and Excel will do the rest:
This formula uses the TEXT function which orders Excel to transform any values into text using any format used in the formula, “ddd” is used to display abbreviated weekday names (Sat, Sun) and “dddd” is used to display their full names (Saturday, Sunday).
You can also use the CHOOSE function for a more personalized way to get the day name from a date.
This formula will let you input any desired weekday name you want to be shown because the WEEKDAY function is used in the formula to pick a weekday number, which in turn will be used by the CHOOSE part to display its corresponding value, the first value represents Sunday and the last one represents Saturday.
While this formula is more tedious, it allows for more customization because it lets you change the weekday names. For example, if you use this formula:
It will display any work days (Mondays to Thursday) as “Work” and weekend days (Fridays to Sundays) as “Fun”
Figure 2. Get the day name from a date, CHOOSE and WEEKDAY function example