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
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:
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.
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.
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:
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.
Best Office Productivity Tools
Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel
Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...
Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.
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!