# Calculate Difference between Two Dates

18161 21
cancel
Showing results for
Did you mean:
4 - Data Explorer

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

21 Replies 21
14 - Jupiter

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.

5 - Automation Enthusiast

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!

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…

5 - Automation Enthusiast

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!

@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'
)
``````
5 - Automation Enthusiast

Got it! Thanks a lot!!!

4 - Data Explorer

Jeremy- this is exactly what I am trying to figure out! I added all the columns you specified however when I enter the formula in for “term remaining” I get an error message? Any tips or tricks here? I am entering exactly as pictured in the thread and have all required fields loaded (years remaining, months remaining, days remaining) successfully.

4 - Data Explorer

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!

16 - Uranus

`IF(OR(AND({Start Date}>TODAY(),{End Date}>TODAY()),{Start Date}={End Date}),"Completed",<add the nested IF formula from the example you provided here>)`