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

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

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.

1 Like