Convert numbers of days to year/month/day in Excel and Google Sheets
=DATEDIF(0,A2,"y")&" years " &DATEDIF(0,A2,"ym")&" months "&DATEDIF(0,A2,"md")&" days"
A2 = data cell
Check below for a detailed explanation with pictures and how to use formulas in Excel and Google Sheets.Convert numbers of days to year/month/day in Excel
How to convert numbers of days to year/month/day in Excel?
CONVERT NUMBERS OF DAYS TO YEAR/MONTH/DAY — EXCEL FORMULA AND EXAMPLE
=DATEDIF(0,A2,"y")&" years " &DATEDIF(0,A2,"ym")&" months "&DATEDIF(0,A2,"md")&" days"
A2 = data cell
DATEDIF = calculates the number of days, months or years between two dates.
‘Y’: the number of complete years in a period or between start_date and end_date.
‘M’: the number of complete months in a period or between start_date and end_date.
‘D’: the number of days in a period or between start_date and end_date.
‘MD’: the number of days in a period after subtracting whole months.
‘YM’: the number of whole months in a period after subtracting whole years.
‘YD’: the number of days between start_date and end_date, assuming start_date and end_date were no more than one year apart.
Convert numbers of days to year/month/day in Google Sheets
How to convert numbers of days to year/month/day in Google Sheets?
CONVERT NUMBER WITH TWO DECIMAL — GOOGLE SHEETS FORMULA AND EXAMPLE
=DATEDIF(0,A2,"y")&" years " &DATEDIF(0,A2,"ym")&" months "&DATEDIF(0,A2,"md")&" days"
A2 = data cell
DATEDIF = calculates the number of days, months or years between two dates.
‘Y’: the number of complete years in a period or between start_date and end_date.
‘M’: the number of complete months in a period or between start_date and end_date.
‘D’: the number of days in a period or between start_date and end_date.
‘MD’: the number of days in a period after subtracting whole months.
‘YM’: the number of whole months in a period after subtracting whole years.
‘YD’: the number of days between start_date and end_date, assuming start_date and end_date were no more than one year apart.
- Combine date and time
- Convert date to text
- Convert time to text
- Convert seconds to time format
- Convert 1-12 to month name
- Convert month name to number
- Convert date to month and year
- Convert numbers of days to year/month/day
- Dynamic current date and time
- Dynamic current time
- Dynamic today and yesterday date
- Convert number stored as a text to date
- Convert weekday string to number
- Sum by month
- SUMIF cells if contains part of a text string
- Sum total sales based on quantity & price
- Count blank cell
- Count cell between two values
- Combine two or more cell
- Extract left before first space
- Extract the nth words in a text string
- Flip the first and last name
- SUMIF formulas and example
- SUMIFS formulas and example
- COUNTIF formulas and example
- IF formulas and example
- TEXT formulas and example
- CONVERT formulas and example
- Change the negative number to zero
- Change negative to positive numbers
- Calculate percentage changes for containing zero or negative values
- Count cells over 10 characters
- Count cells that are not blank
- Count if cells contain a specific text
- Count cells that begin with a specific text
- COUNTIF based on multiple criteria