How to Convert a Number to a Date in Excel Using Formulas?
Are you working with a spreadsheet in Microsoft Excel that contains numbers representing dates, but they aren’t formatted properly as dates? You can easily convert a number to a date in Excel using a simple formula. In this article, we’ll show you step-by-step how to use the TEXT function along with custom number formatting to transform those numerical date values into formatted dates.
Whether you need to change a 5-digit number like 44196 to a date like 12/31/2020, or convert a huge number like 401769600 to a date format, these Excel formulas and techniques will help you get the job done quickly.
Understanding How Excel Stores Dates
Before we dive into the actual formulas to convert numbers to dates, it’s important to understand a bit about how Excel handles and stores dates behind the scenes.
Excel stores dates as sequential numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Excel for Mac uses a different date system, where January 1, 1904 is serial number 0.
This system enables Excel to correctly calculate the number of days between two dates. For example, if you enter the formula =B2-A2
where A2 contains the date 1/1/2020 and B2 contains 12/31/2020, Excel will calculate the result as 365, the number of days between those two dates in the year 2020.
So in essence, any integer from 1 to 2958465 can represent a valid date in Excel for Windows. The challenge arises when you have a spreadsheet where dates are stored as raw numbers that aren’t formatted properly as dates. Let’s look at how to fix that.
Using TEXT and Custom Formatting to Convert Numbers to Dates
The core Excel formula for converting a number to a date uses the TEXT function combined with custom number formatting. Here are the steps:
- Locate the cell that contains the number you want to convert to a date
- In an adjacent empty cell, enter the formula:
=TEXT(A2,"mm/dd/yyyy")
Replace A2
with the actual cell reference containing your number. Adjust the “mm/dd/yyyy” part based on your preferred date format (e.g. “dd/mm/yyyy”, “mm-dd-yy”, etc.)
- Instead of typing out the format manually, you can also use a custom format code:
=TEXT(A2,0)
The 0
tells Excel to use the custom date formatting applied to that cell. So after entering the formula, apply your desired date formatting to the cell.
- Press Enter to get the result, which will be your original number converted to a properly formatted date!
For example, let’s say you have the number 44196 in cell A2 that represents the date 12/31/2020. Here’s how you would convert it:
- In cell B2 enter:
=TEXT(A2,"mm/dd/yyyy")
- Press Enter
- Cell B2 will show: 12/31/2020
The TEXT function takes the number and displays it in the specified date format. Meanwhile, the custom formatting ensures it looks like a proper date.
Handling 5-Digit Numbers
If your number is 5 digits, like 44196, Excel assumes it represents the number of days since January 1, 1900. So 44196 is December 31, 2020. You can convert 5-digit numbers to dates using the same TEXT
formula described above.
Some other example conversions:
Number | Date |
---|---|
1 | 1/1/1900 |
15000 | 1/24/1941 |
30000 | 3/17/1982 |
44196 | 12/31/2020 |
Working with Very Large Numbers
For much larger numbers, like 401769600, Excel assumes they are the number of seconds (not days) since January 1, 1900. The number 401769600 represents 4654 days and 13 hours since that starting date.
To convert a very large number to a date, you need to divide the original number by 86400, which is the number of seconds in a day (24 * 60 * 60 = 86400):
=(A2/86400)+DATE(1900,1,1)
Breaking this down:
A2/86400
divides the large number by 86400 to get the number of daysDATE(1900,1,1)
returns the date value of January 1, 1900- Adding those together offsets the result by the number of days since 1/1/1900
Continuing our example, if you have 401769600 in cell A2, you would enter:
=(A2/86400)+DATE(1900,1,1)
The result is 5/16/1912, meaning 401769600 seconds is equivalent to the date May 16, 1912.
Accounting for Excel’s 1900 and 1904 Date Systems
As mentioned earlier, Excel for Windows uses 1/1/1900 as the starting point for its date system while Excel for Mac starts at 1/1/1904.
For very large numbers, this means the formula needs to be adjusted slightly for Excel for Mac:
=(A2/86400)+DATE(1904,1,1)
This offsets the result by the number of days since 1/1/1904 instead of 1/1/1900.
Alternative Methods for Converting Numbers to Dates
While using the TEXT
function with custom formatting is the recommended way to convert numbers to dates in Excel, there are a couple alternative methods worth mentioning.
Using Paste Special
If you have a column of numbers that you want to convert to dates, you can use Excel’s Paste Special feature:
- Select the cells containing the numbers
- Copy the cells (Ctrl+C on Windows or Cmd+C on Mac)
- Right-click on the cell where you want the dates
- Choose Paste Special
- Select “Values” and “Add”
- Click OK
This will convert the numbers to dates using Excel’s default date system (1900 or 1904 depending on your version of Excel). You can then apply your desired date formatting to the cells.
Using the DATE Function
For 5-digit numbers, you can use the DATE
function to convert them to dates:
=DATE(1900,1,A2)
This assumes the number in cell A2 represents the number of days since 1/1/1900. The DATE function then constructs a date by adding that number of days to January 1, 1900.
However, this method doesn’t work for larger numbers that represent seconds, which is why the earlier formulas that divide the original number by 86400 are needed.
Troubleshooting Common Issues
Here are a few common issues you might encounter when converting numbers to dates in Excel and how to resolve them:
Dates Appear as Numbers
If your converted dates are still showing up as numbers, it usually means the cell formatting is set to General or Number instead of Date. To fix this, select the cells, go to the Home tab, and choose a date format from the Number Format dropdown.
Dates Are Off by 4 Years
If your converted dates seem to be off by 4 years, it’s likely because your spreadsheet is using the 1904 date system instead of the 1900 system (or vice versa). To check this, go to File > Options > Advanced and look under the “When calculating this workbook” section to see if the “Use 1904 date system” option is checked. You can change it here if needed.
#VALUE! Error
If you see the #VALUE! error after trying to convert a number to a date, it usually means the original value isn’t a valid Excel date serial number. Double-check that your number is indeed a proper date representation and that you’re using the right conversion formula for your number size (5-digit vs. very large numbers).
Final Thoughts
Converting numbers to dates is a common task in Excel, whether you’re dealing with 5-digit numbers representing days or huge numbers representing seconds. The TEXT
function, combined with custom number formatting, provides a straightforward way to convert numbers to dates in Excel.
For 5-digit numbers, use:
=TEXT(number,"mm/dd/yyyy")
And for very large numbers, use:
=(number/86400)+DATE(1900,1,1)
Keep in mind Excel’s date systems start at 1/1/1900 for Windows and 1/1/1904 for Mac. With these formulas in your tool belt, you can easily transform any numerical date value into a properly formatted date in Excel. This will make your spreadsheets more readable, understandable, and easier to work with when building calculations and reports involving dates.
FAQs
What is the basic formula to convert a number to a date in Excel?
The basic formula to convert a number to a date in Excel is: =TEXT(number,"mm/dd/yyyy")
. Replace “number” with the cell reference containing the numeric value and adjust the date format as needed.
How do I convert a 5-digit number to a date in Excel?
To convert a 5-digit number to a date in Excel, use the formula: =TEXT(number,"mm/dd/yyyy")
. Excel assumes that 5-digit numbers represent the number of days since January 1, 1900.
How can I convert a large number (like 401769600) to a date in Excel?
To convert a large number to a date in Excel, use the formula: =(number/86400)+DATE(1900,1,1)
. This assumes that the large number represents the number of seconds since January 1, 1900. The formula divides the number by 86400 (the number of seconds in a day) and adds it to the Excel date serial number for January 1, 1900.
What should I do if my converted dates are off by 4 years?
If your converted dates are off by 4 years, it’s likely because your Excel spreadsheet is using the 1904 date system instead of the 1900 date system (or vice versa). To check and change this setting, go to File > Options > Advanced and look under the “When calculating this workbook” section for the “Use 1904 date system” option.
Why do I see a #VALUE! error when trying to convert a number to a date?
If you see the #VALUE! error after trying to convert a number to a date, it usually means the original value isn’t a valid Excel date serial number. Double-check that your number is indeed a proper date representation and that you’re using the correct conversion formula for your number size (5-digit vs. very large numbers).
Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.