![\"\"](\"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2020\/10\/17.jpg?resize=1024%2C576&ssl=1\")
<\/p>\n<\/p>\n
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.<\/p>\n
Formula<\/strong> =TEXT(DATEDIF(A2,B2,\u201dy\u201d),\u201d#\u5e74;;;\u201d)&TEXT(DATEDIF(A2,B2,\u201dym\u201d),\u201d#\u6708;;;\u201d)&TEXT(DATEDIF(A2,B2,\u201dmd\u201d),\u201d#\u5929;;;\u201d) <\/em><\/strong><\/p>\n<\/div>\n<\/div>\n<\/div>\n Formula Explanation<\/strong><\/p>\n The format here is separated by three semicolons, representing four different formats for the following four values:<\/p>\n \u201c#\u5e74;;;\u201d means that when the content is a positive number, it will be displayed in the \u201c#\u5e74\u201d 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 \u201c#\u5e74\u201d format, or nothing will be shown.<\/p>\n To calculate the number of years, months, and days betw […]<\/p>\n","protected":false},"author":1,"featured_media":3120,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[49,8,7,20,18],"tags":[161,162],"class_list":["post-1525","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-date","category-excel","category-excel-formulas","category-onlinesupport","category-text","tag-datedif","tag-text"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2020\/03\/Excel-new10.png?fit=2102%2C679&ssl=1","_links":{"self":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts\/1525","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/comments?post=1525"}],"version-history":[{"count":7,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts\/1525\/revisions"}],"predecessor-version":[{"id":3128,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts\/1525\/revisions\/3128"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/media\/3120"}],"wp:attachment":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/media?parent=1525"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/categories?post=1525"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/tags?post=1525"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nThe formula is:<\/p>\n\n\n\n\n
DATEDIF(A2,B2,\u201dy\u201d)<\/code>: 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.<\/li>\nTEXT(DATEDIF(A2,B2,\u201dy\u201d),\u201d#\u5e74;;;\u201d)<\/code>: Displays the whole year number obtained from\u00a0DATEDIF(A2,B2,\u201dy\u201d)<\/code>\u00a0in the format \u201c#\u5e74;;;\u201d.<\/li>\n<\/ul>\n\n
\n
DATEDIF(A2,B2,\u201dym\u201d)<\/code>: 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.<\/li>\nTEXT(DATEDIF(A2,B2,\u201dym\u201d),\u201d#\u6708;;;\u201d)<\/code>: Works the same as the previous\u00a0TEXT(DATEDIF(A2,B2,\u201dy\u201d),\u201d#\u5e74;;;\u201d)<\/code>.<\/li>\nTEXT(DATEDIF(A2,B2,\u201dmd\u201d),\u201d#\u5929;;;\u201d)<\/code>: Functions similarly to\u00a0TEXT(DATEDIF(A2,B2,\u201dy\u201d),\u201d#\u5e74;;;\u201d)<\/code>.<\/li>\n<\/ul>","protected":false},"excerpt":{"rendered":"