Dates allow you to track when an event occurs. However, a date is not always specific enough. After all, if I asked you what day the eighth of December 2032 would be, you probably wouldn’t guess it’s a Wednesday— and that’s the problem. When we can match the date to a day, we can better determine how to plan for the event.
Fortunately, you can take advantage of functions in Excel that convert dates into days. Alternatively, format your dates as “Long Date,” which includes the date’s day.
How to get day name from date in Excel:
- Launch a Microsoft Excel client
- Create/open a sheet
- Reserve cells for your date’s days
- Click on one of the reserved cells
- Enter the following:
=TEXT(CELL,"ddd") - Replace “CELL” with the cell containing your date
If you’re working with dates in Excel but unhappy with the formatting, you might consider trying to illustrate them as long dates or individual days. In which case, you’ll be pleasantly surprised to know that both are straightforward processes. In addition, I’ll cover both of them with in-depth, step-by-step guides, so don’t worry if you’re unfamiliar with them.
The following post features a handful of sections, but I respect that you might not care about all of them. So, I have provided the following shortcuts that enable you to quickly access the most relevant page.
- How to get day name from date in Excel
- How to turn short dates into long dates in Excel
- How to fix dates replaced with # in Excel
- Use the Format Cells dialog box to get day name from date in Excel
- Use the WEEKDAY function to get day name from date in Excel
- How to change a simple date into a fully formatted date
- Blog post recap
How to get day name from date in Excel
One of two ways you can identify a date’s day in Excel is to utilize a formatting function. In doing so, we can preserve our short dates while still observing the date’s respective day— this is ideal for smaller tables. Below, you will find a preview of what we’ll be recreating.
Step 1 — Launch your preferred Excel client
To start, launch your preferred Excel client. For the guide below, I’ll be using the desktop application. However, you should be able to follow along on the web service (linked here). Otherwise, hit the “Start” button, type “Excel,” and hit “Enter.”
Step 2 — Open an Excel Spreadsheet
Once you’ve launched Excel, you need to open an existing sheet or create a new one; however, if you make a new sheet, you will need to create a table featuring dates.
Step 3 — Make some space for the days
If you’re creating a new sheet or adjusting an old one, you’ll want to allocate space for each date’s day. In the example below, I have created a “DOB Day” and a “BD Day” column. However, yours may look different.
Step 4 — Input the following function
Within each of your allocated cells, you will need to input the function I have provided below. In doing so, you will be able to illustrate each day in its shorthand format. If you want to use the days’ full names, you can replace the “ddd” with “dddd.”
=TEXT(CELL,"ddd")
Bonus — Recreate the effect
You may have noticed that a selected cell possesses a square shape in its bottom right corner. By selecting and dragging the square, you can create a selection of cells wherein you would like to recreate the effects of the initial cell. As shown below, this will work for multiple formulas and functions, including the one in the following guide.
How to turn short dates into long dates in Excel
If you don’t want to waste time allocating additional space for days, you can simply format the existing date cells to appear as long dates that feature their respective day.
Number formatting
First, select the cells containing your dates. If you have separated dates between columns, you can hold ctrl to expand your selection. After you have completed your selection, head to the “Home” tab and open the dropdown under “Number.” In the menu, find and select “Long Date.”
How to fix dates replaced with # in Excel
Under certain circumstances, you may find that Excel isn’t displaying a cell’s content correctly. More specifically, the cell’s content is replaced explicitly with “#” symbols. Fortunately, this issue isn’t notable. We can fix it without difficulty.
Step 1 — Select the affected columns
First, select the header columns wherein the issue is present. If you have separated dates between columns, you can hold ctrl to expand your selection.
Step 2 — Adjust your selected columns’ width
Click, hold, and drag one of the selected header columns’ borders. Moving the border further right to make it larger. Doing so will resolve the issue. I recommend setting a width of 180 pixels for long dates.
Use the Format Cells dialog box to get day name from date in Excel
 Note: The following method is for the app version of Excel. |
- Select the cells to convert into weekday names.
- Then go to “Home” and click the pop-out
icon in the “Number” section.
- Click on “Custom” on the left menu.
- Then set “Type” to “ddd” for the shorthand day name or “dddd” for the longhand name.
- Finally, click “OK.”
Result:
Use the WEEKDAY function to get day name from date in Excel
You can use the WEEKDAY function to change a date into a numeric day. This can also be achieved by using the TEXT function. However, the difference is that the WEEKDAY function will return the numbers 1-7 corresponding to the day of the week (1 being Sunday and 7 being Saturday). The TEXT function returns the day of the month in a numeric format so that 10/10/2022 would return 10.
=TEXT(CELL,"d")=WEEKDAY(CELL)- In this instance, the “CELL” is “A1.”
- Type or paste the code into an adjacent cell.
- Then drag the formula to affect multiple rows or columns.
Combine the WEEKDAY and CHOOSE functions
You can also combine the WEEKDAY function with CHOOSE to return the textual days of the week or any value you like. Copy and paste the code below and paste it into an adjacent cell.
=CHOOSE(WEEKDAY(CELL),"Sun","Mon","Tue","Wed","Thur","Fri","Sat")You can modify the text inside the quotations to return any value you like. For example, if you wanted the long-form day name, you could change “Sun” to “Sunday” and so on.
- In this instance, the “CELL” is “A1.”
- Type or paste the code into an adjacent cell.
- Then drag the formula to affect multiple rows or columns.
How to change a simple date into a fully formatted date
If you want to change a simple numeric date into a fully formatted date, you can do that using the TEXT function and IF statements. Copy and paste the following code into an adjacent cell.
=TEXT(A1, "dddd d") &
IF(OR(DAY(A1)=1, DAY(A1)=21, DAY(A1)=31), "st",
IF(OR(DAY(A1)=2, DAY(A1)=22), "nd",
IF(OR(DAY(A1)=3, DAY(A1)=23), "rd", "th")))
& TEXT(A1, " mmmm yyyy ")- In this instance, the cell is “A1.”
- Type or paste the code into an adjacent cell.
- Then drag the formula to affect multiple rows or columns.
Blog post recap
To conclude, we can extract an Excel date’s day in two ways. The first revolves around using a function to create a new formatted value (using the old cell and value). The second method involves using Excel’s number formatting tool to change our short dates into long ones (that include the day). Sometimes, when a cell cannot contain a value, it will be depicted as several “#” symbols— you can fix this issue by expanding the cell’s width.
Thank you for reading this post. If you enjoyed the post or found our content helpful, consider trying some of our other Microsoft 365 guides. Each writer works hard to construct reliable resources for your benefit— we appreciate your support.