How to Get the Name of a Day from a Date in Excel

Got a series of dates in Excel and want to know how to get the name of a days they fall on? Here’s a quick Excel guide to help you with that using a simple formula with some variations. Here’s how to get the name of a day from a date.

How to Get the Name of a Day from a Date in Excel

Full Form

There are a few different ways in which you can get this information. Let’s say you only want the full name returned, such as Tuesday, you need to make use of the formula : =TEXT(A1,”dddd”)

So if we wanted to know when 10-8-16 fell on, we enter the formula in another cell to get the result as Wednesday.

Abbreviated Form

If on the other hand, if you want a day showing up only in the abbreviated format, you use the formula, =TEXT(A1,”ddd”). This will return the result, Wed.

Custom Format

If on the other hand, you want to spice up your day names with something more snazzy, you can create a custom name list and get Excel to display that name instead of the standard names. The function is:

=CHOOSE(WEEKDAY(A1),”XXX Sunday “, “XXX Monday”, “XXX Tuesday”, “XXX Wednesday”, “XXX Thursday “, “XXX Friday”, “XXX Saturday”)

where XXX is the custom name you give to the day.

For example, your formula can read like this:

=CHOOSE(WEEKDAY(A1),”Happy Sunday “, “Moody Monday”, “Dowdy Tuesday”, “Mayhem Wednesday”, “Thirsty Thursday”, “Freaky Friday”, “Sleepy Saturday “)

where XXX is the custom name you give to the day.

In this function, we use the WEEKDAY function, which returns a number from 1 to 7. 1 is indicative of Sunday and 7 of Saturday. If you want to change which day of the week starts with 1 you’ll need to append a number to the weekday function.

When you enter the function =WEEKDAY(A1), it will return the result of 1, which is a Sunday. If cell A1 has the date we used in our example, 10-8-16, it will return a result 4.

If you want Monday to be the starting day of the week, you use this function:

=WEEKDAY(A1, 2) and if you want Tuesday to be the starting day of the week, you use the function=WEEKDAY(A1, 3). In our example, if we used =WEEKDAY(A1, 2) on the date 10-8-16, it will return a result 3.

So back to our custom dates. Let’s use the function, =CHOOSE(WEEKDAY(A1),”Happy Sunday “, “Moody Monday”, “Dowdy Tuesday”, “Mayhem Wednesday”, “Thirsty Thursday”, “Freaky Friday”, “Sleepy Saturday “) on our date. Here’s what we get in return:

So that’s it folks! Getting the name of a day from a date is easy if you remember these simple functions and rules.

If you found this tutorial helpful, be sure to check out more good stuff in our Learn Excel series. If you want to learn Excel from scratch, consider taking up our Udemy course, Excel 2016 for Beginners. It is available at a nominal price of just $10.