Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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

Topic Options

- 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
18161
21

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- 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

Sep 06, 2018 11:00 AM

Hi @Anet_Gambina1,

Here’s what I’d do in order to get a variable display of the Term Remaining:

First, the result:

In the “Years Remaining” Field:

```
DATETIME_DIFF({Lease Expiration}, TODAY(), 'years')
```

In the “Months Remaining” Field:

```
DATETIME_DIFF({Lease Expiration}, TODAY(), 'months')
```

In the “Days Remaining” Field:

```
DATETIME_DIFF({Lease Expiration}, TODAY(), 'days')
```

You can then HIDE those 3 fields from your view.

In the “Term Remaining” Field:

```
IF(
{Months Remaining}>0,
IF(
{Years Remaining}>0,
{Years Remaining}) &
IF(
{Years Remaining}>1, "yrs ",
IF(
{Years Remaining}=1, "yr "
)
) &
({Months Remaining}-{Years Remaining}*12) &
IF(
OR(
({Months Remaining}-{Years Remaining}*12)>1,
({Months Remaining}-{Years Remaining}*12)=0
),
"mos ",
IF(
({Months Remaining}-{Years Remaining}*12)=1,
"mo "
)
),
{Days Remaining} &
IF(
{Days Remaining}>1," days",
IF(
{Days Remaining}=1," day"
)
)
)
```

That gives you a nice display of the term remaining that changes format based on how long is left.

Reply

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

Jan 07, 2019 07:20 PM

Hi Jeremy,

Thank you so much for this!!! My coding skills are not good and I have been trying to figure this on my own.

I noticed that this only works for combinations of years and months so lets say just for the sake of this example that the remaining duration would be 1 year & 6 months & 28 days. Then it will not display the 28 days when using this formula. It wont display 28 days until it goes to a full month which should be then 1 year & 7 months.

Can you give a modified version of this code to include the remaining 28 days?

Your time would be appreciated! I just cant seem to work on the logic of the code.

Thanks again!

Reply

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

Jan 08, 2019 04:44 AM

Well, it’s a little more complicated than that — which is why Jeremy only had `{Days Remaining}`

kick in when no whole years or months remained.

Unsurprisingly, the number of days in a month, according to `DATETIME_DIFF()`

, depends on the month — seemingly, the month in which the time period begins. Accordingly, your example of ‘`1 yr 6 mos 28 days`

’ would not be valid when `TODAY()`

falls in a non-Leap Year February: If there were 28 days left, the function would return ‘`1 yr 7 mos`

’. Similarly, during other months of the year, the ‘month’ counter might turn over on day 29, 30, or 31.

Take a look at this snippet:

These are the maximum values for ‘2 months and *X* days’ for the period between `{Today}`

and `{Lease Expiration}`

; that is to say, 1 more day added to any of these periods would raise `{Months Remaining}`

to `3`

and would presumably represent `3 mos 0 days`

.

The question is: How would I arrive at the value of `*X*`

for each of these periods?

Going with a 30-day month, as you seem to suggest, would result in the last two records having `{Term remaining}`

s of, respectively, ‘`2 mos 31 days`

’ and ‘`2 mos 30 days`

’. (Personally, I feel those two records should evaluate to *3* months; one of Jeremy’s examples also gave us a 90-day, 2-month period. I understand why the code returns the value it does — it’s giving us the number of *whole* months — but it can be confusing.)

Seemingly, the only way to give an arguably standard number of remaining days would require a `SWITCH()`

statement that branched based on the month of `TODAY()`

. Or maybe it would need to take the expiration month into consideration as well; it’s late, and I’m not thinking too clearly. In any case, it adds at least another order of complexity to the formula — and whatever approach one decides to take, there are equally compelling arguments for the others.

@Jeremy_Oglesby chose one, which ignores remaining days unless less than a month (for a certain value of ‘month’) remains. Another might be to declare all months to have 30 days, use a `MOD()`

function to calculate how many remain, and ignore the several stretches of time each year where the day count becomes hard to justify. Another possibility might be to do a similar `MOD()`

calculation, divide by 30, and use that value to round up the number of weeks remaining, if appropriate. Pick the one that best fulfills your needs…

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

Jan 10, 2019 04:24 AM

I have a similar problem, I have to calculate a kid’s age in years and at least months from his DOB to today. How would you suggest doing that? My actual formula just shows off years:

```
DATETIME_DIFF(TODAY(),{Cumpleaños}, 'years')
```

Thank you!

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

Jan 10, 2019 07:32 AM

@Jeremy_Oglesby’s approach works great for years-and-months values: Calculate `{Age in Years}`

and `{Age in Months}`

and… actually, you can simply it a bit from the original, I just noticed. Try

```
{Age in Years} & ' years' &
IF(
MOD({Age in Months,12)!=0,
' and ' & MOD({Age in Months},12) & ' months'
)
```

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

Jan 10, 2019 08:12 AM

Got it! Thanks a lot!!!

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

Mar 13, 2019 11:32 AM

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

Mar 19, 2019 12:37 PM

Hello. I’ve used the functions found herehttps://community.airtable.com/t/calculate-difference-between-two-dates/17038 to calculate the difference between two dates.

My problem is I have a few scenarios where Start Date and End Date are the same. Instead of negative days I need them to say “Complete” and for dates that have Start and End Dates that have already passed I need them to read “Complete” as well.

Any help is much appreciated!

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

Mar 19, 2019 02:08 PM