Help

Re: Calculate Difference between Two Dates

9067 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Anet_Gambina1
4 - Data Explorer
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’)

lease-formula

Thank you!

Anet Gambina

22 Replies 22

Hi @Anet_Gambina1,

Here’s what I’d do in order to get a variable display of the Term Remaining:
First, the result:
image.png

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.

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:

days2months.png

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…

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

Got it! Thanks a lot!!!

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.

Mackenzie_Covin
4 - Data Explorer
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!

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