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. Fix Excel Date Serial Number Formatting Issue TODAY EDATE =EDATE(TODAY(), 6 Check Out: 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. =EDATE(TODAY(), 6 Number Format Quick Fix Excel Date Serial Number Formatting Issue – Simple Steps You can change the Number Format via two ways. Number Format Using a Shortcut
Using the Home Tab Using a Shortcut Using a Shortcut Using the Home Tab 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) =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. 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. 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. Format Cells Category Number Formats Date 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. 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. Date Type 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. Let’s assume we are executing the same function =EDATE(TODAY(), 6), and we are getting the output 45915. =EDATE(TODAY(), 6) 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. 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. Short Date Long Date I am choosing the Short Date. 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? Fix Excel Date Serial Number Formatting Issue How to Fix Excel Date Serial Number Formatting Issue?

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

Quickly Return Hour Values with Excel's HOUR Function!

Excel NOW Function: Real-Time Date & Time in Your Sheets

View Website Here

Read My Stories

Too Long; Didn't Read

Make resilience your competitive advantage

Excel Dates Showing Numbers? Here's the Quick Fix

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

Excel Formulas for Assigning Categories by Value Range Using Functions!

4 Data Transformations Made Spreadsheet-Easy

5 Excel Features That Can Grow Your Net Worth in No Time

5 Ways to Add Security to Excel & PDF with .NET Document APIs

7 Essential Things in Excel That You Definitely Need to Master

Excel Formulas for Assigning Categories by Value Range Using Functions!

4 Data Transformations Made Spreadsheet-Easy

5 Excel Features That Can Grow Your Net Worth in No Time

5 Ways to Add Security to Excel & PDF with .NET Document APIs

7 Essential Things in Excel That You Definitely Need to Master

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps