Fix Excel Date Serial Number Formatting Issue: When you are working with DATE Functions in Excel like TODAY, EOMONTH, EDATE Functions, etc., the output may be confusing. For instance, upon executing =EDATE(TODAY(), 6) Function assuming today as March 14, 2025, the output we expect will be of Date, rather, we get the output as 45914. What exactly happens behind this is quite interesting.
Check Out:
Why does this Error occur?
By default, Excel stores Dates as Serial Numbers starting from January 01, 1990 which means January 01, 1990 is considered 1st day. Hence, March 14, 2025 will be the 45914th day and this is what is shown as the output. The =EDATE(TODAY(), 6) Function assuming today as March 14, 2025 has been executed successfully and in a way, 45914 is the right answer though, however, we are expecting the output in the Date Format and that is the answer. We just have to change the Number Format of this output.
Quick Fix Excel Date Serial Number Formatting Issue – Simple Steps
You can change the Number Format via two ways.
- Using a Shortcut
- Using the Home Tab
Using the Shortcut
This is the quickest way to fix Excel Date Serial Number Formatting Issue. For explanation, let’s assume, we are executing an EDATE Function as follows.
=EDATE(TODAY(), 6)
- The above function is supposed to return the exact date 6 months from today. Instead of the Date output, we get the value as 45915. This is because Today (March 15, 2025) is the 45915th day from January 01, 1990. Here is the fix.
- Select the Cell and the press CTRL + 1.
- Format Cells windows will open now. Under Category, you can find various Number Formats, from General, Number, Currency, Accounting, to Time, Percentage, Special, and more. Here, select the Date Format.
- On the Right-side, you will have various Date Types, for example, a date can be MM-DD-YY, or DD-MM-YYYY, or DD-MONTH-YY, and so on. Choose your preferred Date Type and then click OK.
- Now, the output will be of Date Format.
Using the Home Tab
This is an alternate way to fix Excel Date Serial Number Formatting Issue. The following are the steps.
- Let’s assume we are executing the same function
=EDATE(TODAY(), 6), and we are getting the output 45915. - Select the output cell where the value is shown in a different format, and click Home and then go to Number.
- Dropdown and you can select either Short Date or Long Date. Short Date will be of MM/DD/YYYY, whereas, the Long Date will display value as DAY, Month, DD, YYYY. Choose accordingly.
- I am choosing the Short Date.
Take Away
The above explained two methods provide detailed solution to Fix Excel Date Serial Number Formatting Issue. If you are working with a list of Dates through an entire column, then you can select the entire column and switch it to the Date Format using any of the above two methods. This article is originally published on How to Fix Excel Date Serial Number Formatting Issue?
