A Complete Guide On: How To Convert Number To Date In Excel
Jun 06, 2023
Do you need help thinking about converting numbers to dates in Excel?
If so, we have good news. You can easily convert those numbers into dates with our guide and the right formula! In this blog post, we'll explain exactly how to do it.
We will talk about various methods that could be effective based on your specific circumstances. Moreover, we'll discuss any potential challenges during the process.
Read on as we cover the following:
-
Converting Numbers to Date in Excel
-
Learn the Different Ways to Convert Numbers to Dates in Excel
-
Final Thoughts on How to Convert Numbers to Date in Excel
-
Frequently Asked Questions on Converting Numbers to Date in Excel
Converting Numbers to Date in Excel
Excel uses serial numbers to store dates and times; a cell's format determines the number displayed as a date.
Moreover, the dates are stored as numbers, with 1-Jan-1900 represented as 1 and 2-Jan-1900 as 2. Similarly, 1-Jan-2015 is also 42005.
When you use various date functions to calculate dates in Excel, the result is usually a serial number or different date format representing a date.
If the formula =TODAY()+7 returns a number like 44286 instead of the date format, it might not be a mistake.
Learn the Different Ways to Convert Numbers to Dates in Excel
Excel has different methods for converting a number to a date value. Here are some of them:
Convert numbers to dates with the Number Format drop-down in Excel.
With the Number format drop-down feature in Microsoft Excel, you can easily convert serial numbers to date format.
We have an example dataset (C2:C6). As we can see in the dataset, the dates are in number format. We'll convert the numbers into date formats.
-
Select the serial numbers.
-
Proceed to the Home tab and look for the Number Format drop-down feature within the Number group.
-
In the Number format drop-down feature, you can choose a date format, either a Short Date format or a Long Date Format.
After choosing a Date format, the numbers formatting changed to date values.
Convert numbers to dates with the Date format option.
Excel has built-in format options to convert numbers to date easily.
Follow the steps below to use the built-in format options:
-
Select all the numbers you want to convert.
-
Right-click the selected cells, then select Format Cells.
-
Go to the Number tab and select the Date Category. Choose a Date format code from the Type box and click OK.
Setting the date formatting will change the number formatting of all the selected cells.
Convert numbers to dates with the Custom Date Format.
You can also make a custom date formatting in Excel to improve the visual appeal of your data.
To make a Custom Date Formatting, follow the steps below:
-
Select your data set, right-click, and choose Format Cells.
-
In the Format Cells dialog box, proceed to the Number tab. Click the Custom format category, type your Custom Date format, and click the OK button.
By following the steps above, all the selected cells will contain your custom date formatting.
Convert numbers to dates with the TEXT function.
The TEXT function can convert numbers into text with a specific format. You can also use the TEXT function for converting numbers into dates.
To learn converting numbers to dates with the TEXT function, follow the steps below:
-
Create a helper column for the TEXT function.
-
Type the TEXT function and choose a cell value.
-
Type the Date format you want to use and enclose it with double quotation marks.
-
Press the Enter key and use the Auto-Fill feature to fill the other rows.
Using the TEXT function when converting numbers to dates is relatively easy. After learning the TEXT function, you can try other date formats in Excel.
Final Thoughts on Converting Numbers to Date in Excel
Now you know to convert numbers into dates in Excel, regardless of the format. Using this skill, you can save time and effort while dealing with comprehensive documents or databases with date-oriented information.
Visit Simple Sheets for more easy-to-follow guides and examples, and remember to read the related articles section of this blog post.
For the most straightforward Excel video tutorials, subscribe to Simple Sheets on Youtube!
Frequently Asked Questions on Convert Number to Date in Excel
Why should I use the DATE function when converting numbers to dates in Excel?
Using the DATE function when converting numbers to dates in Excel is a simple yet incredibly powerful tool for data analysis.
Not only does it streamline the conversion process, but it also ensures accuracy and consistency in your data.
How can I fix text dates with two-number years?
To view and fix this error, hover over the cell and click the drop-down button on the right.
The button has a yellow sign with an exclamation mark, providing options for fixing errors and other useful features.
Can I toggle the text dates two-number year error checking in Excel?
You can enable/disable error checking by following the steps below:
-
Proceed to the File tab and click Options.
-
Click Formulas.
-
Check or Uncheck the box that enables/disables background error checking.
Related Articles:
Quick Guide: How To Insert 3D Models In Excel
Want to Make Excel Work for You? Try out 5 Amazing Excel Templates & 5 Unique Lessons
We hate SPAM. We will never sell your information, for any reason.
