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:
- Launch a Microsoft Excel client
- Create/open a sheet
- Reserve cells for your date’s days
- Click into one of the reserved cells
- Enter the following:
- 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 convert a date into a day in Excel
- How to turn short dates into long dates in Excel
- How to fix dates replaced with # in Excel
- Blog post recap
How to convert a date into a day 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.”
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.
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.
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.