How to convert numbers to year/month/day or date in Excel?
We can easily count the days between two dates in Excel, but how to display the number of days as year/month/day, such as "1 year 4 months 25days"? And what if converting a number yyyymmdd to a normal date? This article is talking about converting numbers to year/month/day or dates in Excel.
- Convert numbers (days) to year/month/day
- Convert numbers (days) to year/month/day with an amazing tool
- Convert general numbers to dates
- Convert general numbers to date with custom date formatting
- Convert number yyyymmdd to date
| Feature | Formula | Kutools Formula Helper | Excel Short Date | Kutools Apply Date Formatting | Kutools Convert to Date |
|---|---|---|---|---|---|
| Best for | Converting a number of days into years, months, and days | Converting days to years/months/days without memorizing formulas | Quickly displaying Excel serial numbers as dates | Converting serial numbers with a chosen date format | Converting yyyymmdd numbers such as 20151215 to dates |
| Output | Text such as 1 year 4 months 25 days | Text such as 1 year 4 months 25 days | Standard Excel date | Date in a selected format | Standard Excel date |
| Main advantage | Built into Excel and flexible | Easier than memorizing a DATEDIF formula | Fast and built into Excel | Provides multiple date-format choices | One-click conversion for yyyymmdd numbers |
| Recommended use | When you need formula-based year/month/day results | When you frequently convert day counts | When 5-digit values are already Excel date serial numbers | When you want a specific date appearance | When imported data contains numbers in YYYYMMDD format |
Convert numbers (days) to year/month/day
This method will introduce a formula to convert the number of days to year/month/day such as "1 year 4 months 25days" in Excel. Please do as follows:
1. Select a blank cell you will output the conversion result, and enter the following formula into it, and press the Enter key.
=DATEDIF(0,A2,"y")&" years " &DATEDIF(0,A2,"ym")&" months "&DATEDIF(0,A2,"md")&" days"
Note: A2 is the cell with number of days that you will convert to year/month/day.
2. Keep selecting the cell of conversion result, and drag its Fill Handle to the range as you need. And then you will see all numbers are converted to year/month/day. See screenshot:
Convert numbers (days) to year/month/day with an amazing tool
Some users may feel hard to memorize formulas to convert the number of days to year/month/days in Excel. Here I recommend the Formula Helper of Kutools for Excel to quickly finish the conversation without memorizing the painful formulas.
1. Select the cell you will put the calculation result at, and click Kutools > Formula Helper > Formula Helper to enable the feature.
2. In the Formulas Helper dialog, please select Date from the Formula Type drop-down list, click to highlight Convert days to year month day in the Choose a formula list box, then specify the number cell in the Number box, and finally click the Ok button.
Now the calculation result is output in the selected cell. If necessary, you can drag the AutoFill Handle of this cell to copy this formula to other cells.
Convert general numbers to date of default date formatting
Sometimes, you type or paste dates into Excel, but the dates show as 5-digits of general numbers. In this case, you can easily convert 5-digits of general numbers to dates with following methods:
Select the 5-digits numbers, and click Home > Number Format box > Short Date. And then you will see all selected 5-digits numbers are converted to dates with default date formatting at once.
Convert general numbers to date with custom date formatting
Kutools for Excel's Apply Date Formatting utility can help you quickly convert 5-digits numbers to dates with custom date formatting.
1. Select the 5-digits of numbers, and click Kutools > Format > Apply Date Formatting.
2. In the opening Apply Date Formatting dialog box, select the specified date formatting you will use in the Date formatting box, and click the Ok button. And then you will see all numbers are converted to dates with specified date formatting. See screenshot:
One click to convert number yyyymmdd to date
Sometimes, you may get some special numbers of yyyymmdd, such as 20151215. This section will show you an easy way with one click to convert these numbers of yyyymmdd to normal dates by the Convert to Date utility of Kutools for Excel.
Select the cells with special numbers of yyyymmdd, and click Kutools > Content > Convert to Date. See screenshot:
And then you will see all selected numbers are concerted to normal dates. See screenshot:
Please click the Close button to close the Convert to Date dialog box.
Conclusion
Excel can handle several types of numeric date data, but the best conversion method depends on what the number actually represents.
Use a formula or Kutools Formula Helper when you need to convert a number of days into years, months, and days. If the values are already Excel date serial numbers, Short Date or Kutools Apply Date Formatting can display them as readable dates. For numbers stored in YYYYMMDD format, Kutools Convert to Date provides a quick way to convert them into standard Excel dates.
Tip: Before converting, first identify whether your values are day counts, Excel date serial numbers, or date strings such as YYYYMMDD. Choosing the correct method will help avoid incorrect results.
In short, Excel’s built-in tools work well for standard cases, while Kutools for Excel can simplify repeated conversions and provide more convenient formatting options.
Related articles:
How to convert date to weekday name or month name in Excel?
How to add / subtract days / months / years to date in Excel?
How to count / calculate weeks / months / years between two dates in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in