Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Other questions
- Calculate Difference between Two Dates

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

1
18172
21

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 06, 2018 06:48 AM

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

Reply

21 Replies 21

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 19, 2019 07:58 PM

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 02, 2019 07:56 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 02, 2019 08:57 AM

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

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 02, 2019 09:43 AM

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?

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 02, 2019 11:40 AM

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

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 03, 2019 08:28 AM

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:

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 21, 2021 08:16 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 21, 2021 08:20 PM

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')
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 21, 2021 09:19 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 21, 2021 10:07 PM