Skip to main content

Calculate date difference in years AND months, and 1 more question on this formula

  • June 9, 2020
  • 3 replies
  • 56 views
S
Forum|alt.badge.img+17

I want to calculate the difference between 2 dates. One is the purchase date of a condo and one is the sale date of that condo. I need the number of years and months to figure out refunded common charges at closing. I have the formula working for years or months. But can it be both? If not, anyone have experience in this scenario and their solution?

And in the same formula, the field shows NaN if there is no data in one of the fields. For current owners the buy date will be populated, but the sell date will be blank. How do I tell the formula to leave the field blank if one field is blank?

Thanks in advance for any help on this.

Best, Scott

3 replies

Kamille_Parks11
Forum|alt.badge.img+27

You could use a formula like this:

IF(AND({Date 1}, {Date 2}), DATETIME_DIFF({Date 2},{Date 1}, 'years') & ' years & ' & (DATETIME_DIFF({Date 2},{Date 1}, 'months') - DATETIME_DIFF({Date 2},{Date 1}, 'years')*12) & ' months')

This will output the difference in time between two dates in this format: # years & # months

The formula is within an IF() statement to only fill in the cell if both date fields have values.

Let me know if this worked for you. If so, please mark this comment as the solution.

V
B
kuovonne
Z
Forum|alt.badge.img+1
  • Zherocode
  • New Participant
  • January 30, 2023

Hey guys!

I just wanted to chime in and offer a small improvement to the code that's already been posted here. You'll enjoy this new code because it automatically handles singular or plural forms so you never have to wonder if it's one year or multiple. Also, the code has been made more readable by removing any mention of "0 months" and "0 years."

For even more precise results, the code uses additional functions such as MOD and IF for a more efficient processing. And lastly, by using the most relevant variable names, the code makes it clear what's happening at each step, improving the overall understanding of the code. In short, with this code, you'll always get accurate, concise, and clear results!

And if you have any questions or need help, don't hesitate to reach out to me. Thanks for reading!

Cheers,
Nicolas
Zherocode

IF(AND({Start Date}, {End Date}), IF(DATETIME_DIFF({End Date},{Start Date}, 'years')=1, "1 year" & IF(MOD(DATETIME_DIFF({End Date},{Start Date}, 'months'),12)=1, " & 1 month", IF(MOD(DATETIME_DIFF({End Date},{Start Date}, 'months'),12)>1, " & " & MOD(DATETIME_DIFF({End Date},{Start Date}, 'months'),12) & " months", "")), IF(DATETIME_DIFF({End Date},{Start Date}, 'years')>1, DATETIME_DIFF({End Date},{Start Date}, 'years') & " years" & IF(MOD(DATETIME_DIFF({End Date},{Start Date}, 'months'),12)=1, " & 1 month", IF(MOD(DATETIME_DIFF({End Date},{Start Date}, 'months'),12)>1, " & " & MOD(DATETIME_DIFF({End Date},{Start Date}, 'months'),12) & " months", "")), IF(MOD(DATETIME_DIFF({End Date},{Start Date}, 'months'),12)=1, "1 month", IF(MOD(DATETIME_DIFF({End Date},{Start Date}, 'months'),12)>1, MOD(DATETIME_DIFF({End Date},{Start Date}, 'months'),12) & " months", "")))), "")

 

A
A
Forum|alt.badge.img+2
  • ar-rm
  • New Participant
  • November 1, 2023

Hey guys!

I just wanted to chime in and offer a small improvement to the code that's already been posted here. You'll enjoy this new code because it automatically handles singular or plural forms so you never have to wonder if it's one year or multiple. Also, the code has been made more readable by removing any mention of "0 months" and "0 years."

For even more precise results, the code uses additional functions such as MOD and IF for a more efficient processing. And lastly, by using the most relevant variable names, the code makes it clear what's happening at each step, improving the overall understanding of the code. In short, with this code, you'll always get accurate, concise, and clear results!

And if you have any questions or need help, don't hesitate to reach out to me. Thanks for reading!

Cheers,
Nicolas
Zherocode

IF(AND({Start Date}, {End Date}), IF(DATETIME_DIFF({End Date},{Start Date}, 'years')=1, "1 year" & IF(MOD(DATETIME_DIFF({End Date},{Start Date}, 'months'),12)=1, " & 1 month", IF(MOD(DATETIME_DIFF({End Date},{Start Date}, 'months'),12)>1, " & " & MOD(DATETIME_DIFF({End Date},{Start Date}, 'months'),12) & " months", "")), IF(DATETIME_DIFF({End Date},{Start Date}, 'years')>1, DATETIME_DIFF({End Date},{Start Date}, 'years') & " years" & IF(MOD(DATETIME_DIFF({End Date},{Start Date}, 'months'),12)=1, " & 1 month", IF(MOD(DATETIME_DIFF({End Date},{Start Date}, 'months'),12)>1, " & " & MOD(DATETIME_DIFF({End Date},{Start Date}, 'months'),12) & " months", "")), IF(MOD(DATETIME_DIFF({End Date},{Start Date}, 'months'),12)=1, "1 month", IF(MOD(DATETIME_DIFF({End Date},{Start Date}, 'months'),12)>1, MOD(DATETIME_DIFF({End Date},{Start Date}, 'months'),12) & " months", "")))), "")

 


@Zherocode Thank you so much!

What a great complete solution that takes into account all of the cases (for `years` and `months`) associated with dates-as-readable-text.

I really appreciate that you posted it here! It saved me a lot of time.

Also thanks to @Kamille_Parks11 

Terms & ConditionsAccessibility statement