Calculate Difference between Two Dates

[Anet_Gambina1]

Hello,

I have two Date fields that need to show the time remaining (in years/months). My attempts at declaring the correct formula have not been successful. Here’s a screenshot with the field outlined where I want to show the difference. I believe this is the closest I’ve come:

DATETIME_DIFF(TODAY(),Rent Commencement,Lease Expiration,‘Years’,‘Months’)

Thank you!

Anet Gambina

[Jesse_Maxwell]

First impression of airtable - it’s absolute garbage that the code to calculate the difference between two dates is more complicated than it is in excel. If the aim is to get people off of things like excel , why make them more complicated?

[Brett_Snelgrove]

Hi there. I am having an issue with using DateTime_Diff. I am using it in a formula to calculate the difference between two dates in months but the result is coming back as neither a month or date.

My formala is:
IF(OR({Start Mth}=0, {End Mth}=0),"",
IF(FY=“19/20”, SUM(DATETIME_DIFF( {End Mth}, {Start Mth}, ‘months’)+1),
IF(FY=“20/21”, SUM(DATETIME_DIFF( {End Mth}, {Start Mth}, ‘months’)+1)
)))

Under Formatting it tells me:
Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.

And at the moment I can’t work out how to make the result a number to then use it in another formula calculation on another field. I discovered Value () but at the moment that is giving me no love.

Btw - I am doing a sum (+1) in there as otherwise DateTime_Diff doesn’t count the End Month:
April 2019 to March 2020 = 12 (but without the sum +1 it comes back as 11)

Any thoughts?

[JonathanBowen]

Hi @Brett_Snelgrove - DATETIME_DIFF gives an integer return value so you should be expecting a number rather than a date. I’m not sure whether your start and end dates are proper dates or formatted date showing just month/year, but leaving that aside for a minute, my recreation of your set up is this:

The Diff field is:

IF(
  AND({End Mth}, {Start Mth}),
  DATETIME_DIFF({End Mth}, {Start Mth}, 'months')
)

Diff + 1 is:

IF(
  AND({End Mth}, {Start Mth}),
  DATETIME_DIFF({End Mth}, {Start Mth}, 'months') + 1
)

A couple of points:

• Rather than saying “if start OR end is 0” a more concise way to do this is to say “if start AND end exist”, i.e. you just need to test for the presence of both for the formula to proceed. If either is empty, then the second part of the IF statement will not be evaluated.
• If the Diff + 1 field I’ve shown the part you refer to in your post, adding 1 to the month. DATETIME_DIFF returns the number of whole months between 2 dates, so in my example I have Diff = 4 (the 4th full month is on 4th Dec, the 5th full month on 4th Jan) and Diff + 1 = 5. As DATETIME_DIFF returns an integer you can just add one to it - no need to do SUM()
• I haven’t incorporated you FY values (although the formula is the same for both, so I’m assuming there’s something else going on in the base that means you need both of these lines.

So…I haven’t given you a complete answer to your problem, but hopefully this gives you enough to correct what you have

JB

[Justin_Barrett]

EDIT: I posted this over an hour ago, but for some reason it sat in limbo for a while. Forgive the apparent duplication of @JonathanBowen’s suggestions.

Are {Start Mth} and {End Mth} date fields, or number fields? If they’re dates, comparing against 0 in your initial IF() is incorrect. I’m guessing you want to check to see if the dates are blank, and only operate on them if they’re filled. For a single date field, the format you want is:

IF({Date Field}, ...

This will execute the rest if {Date Field} isn’t empty. When you’ve got multiple fields to check, you do something similar, wrapping all the checked fields inside AND(). In your case, you’d start like this:

IF(AND({Start Mth}, {End Mth}), ...

Your current format is telling Airtable to fill that formula’s output with an empty string if the dates aren’t there, and a number from one of those later IF() functions if they are. Because a string is one possible output, that’s why Airtable is telling you that it can’t format the field. There are no format options when the output is a string. The structure I’m suggesting will only run the later parts (outputting a number) if both date fields are filled, so the output will either be a number, or an equivalent of BLANK() that will work with numerical calculations.

I’m a little confused by those later two IF() functions because they appear to be exactly the same regardless of whether {FY} contains “19/20” or “20/21”. If that’s what you really want, there’s a much simpler way to approach it. You also don’t need SUM() in there at all from what I can see, because you’re not adding any values. The SUM() format is this:

SUM(value1, value2, ...)

You’re already using +1 to increase the DATETIME_DIFF() result by 1, so SUM() is unnecessary.

Here’s the simplified format for that inner portion only:

IF(OR(FY="19/20", FY="20/21"), DATETIME_DIFF({End Mth}, {Start Mth}, 'months')+1)

Combining everything, you get this:

IF(
    AND({Start Mth}, {End Mth}),
    IF(
        OR(FY="19/20", FY="20/21"),
        DATETIME_DIFF({End Mth}, {Start Mth}, 'months')+1
    )
)

Does that get you what you want?

[JonathanBowen]

@Justin_Barrett - nice that we ended up in a similar place! :slightly_smiling_face:

[Brett_Snelgrove]

Guys, thank you so much. This is very helpful. I also did some tooling around and realised that I could simplify things a bit further to get the result I needed.

IF(ISERROR(DATETIME_DIFF( {End Mth}, {Start Mth}, ‘months’))=0, DATETIME_DIFF( {End Mth}, {Start Mth}, ‘months’))

This now returns the result as a number and also stops ERROR being thrown up if there is no data in End / Start Mth (which sometimes there is.

Also, its great to be learning about some of the quirks of how formulas in AirTable work, especially around OR and AND and even SUM. I’m slowly learning how to write more elegant formulas.

Much appreciated :thumbs_up:

[Andy_Barrows]

So if I wanted to calculate how long someone has been a customer from their start date, I would use DATETIME_DIFF({Start Date}), TODAY(), ‘months’)

That does not seem to work and gives me an error? I just want the output to be a number of months. It could be 34 months, or it could be 1.

[Justin_Barrett]

Check the quotes around “months”. If you copied your formula from a post/comment where the author didn’t style the formula as code, you’ll have curly quotes around that word (‘months’), not straight quotes ('months'). Airtable only likes the latter type. Try this:

DATETIME_DIFF({Start Date}), TODAY(), 'months')

[Andy_Barrows]

Justin_Barrett:

DATETIME_DIFF({Start Date}), TODAY(), 'months')

This did not appear to work, but great catch! Something is still formatted wrong, and I am determined to find it! Thank you for the help!

[Justin_Barrett]

Is the error message giving you anything besides, “Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.”? Could you post a screenshot showing the formula field contents?