To calculate the number of years, months, and days between two dates, and not display the year if it is less than one year, simply subtracting is not enough. Such calculations need to follow a specific formula.
Formula
The formula is:
=TEXT(DATEDIF(A2,B2,”y”),”#年;;;”)&TEXT(DATEDIF(A2,B2,”ym”),”#月;;;”)&TEXT(DATEDIF(A2,B2,”md”),”#天;;;”)
Formula Explanation
DATEDIF(A2,B2,”y”): Finds out how many whole years are between A2 and B2. For example, from May 16, 2019, to June 25, 2019, it is 0 years.TEXT(DATEDIF(A2,B2,”y”),”#年;;;”): Displays the whole year number obtained fromDATEDIF(A2,B2,”y”)in the format “#年;;;”.
The format here is separated by three semicolons, representing four different formats for the following four values:
- Positive format
- Negative format
- Zero value format
- Text format
“#年;;;” means that when the content is a positive number, it will be displayed in the “#年” format; when it is negative, zero, or text, it will be displayed as blank. Since the differences will only yield positive or zero values, if there is more than one whole year, it will be displayed in the “#年” format, or nothing will be shown.
DATEDIF(A2,B2,”ym”): Determines how many whole months (ignoring years) are between A2 and B2. For example, from May 16, 2019, to June 25, 2019, it is 1 month. From February 3, 2011, to November 14, 2012, it is 9 months, not 21 months.TEXT(DATEDIF(A2,B2,”ym”),”#月;;;”): Works the same as the previousTEXT(DATEDIF(A2,B2,”y”),”#年;;;”).TEXT(DATEDIF(A2,B2,”md”),”#天;;;”): Functions similarly toTEXT(DATEDIF(A2,B2,”y”),”#年;;;”).
