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.

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

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

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

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"


      )


    )


)

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…

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!

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

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


    )

{Age in Years} & ' years' & 


IF(


    MOD({Age in Months,12)!=0,


    ' and ' & MOD({Age in Months},12) & ' months'


    )

Got it! Thanks a lot!!!

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

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

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

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"


      )


    )


)

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.

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

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?

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?

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

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?

IF({Date Field}, ...

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

SUM(value1, value2, ...)

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

IF(


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


    IF(


        OR(FY="19/20", FY="20/21"),


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


    )


)

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

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:

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.

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

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!

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?

@Andy_Barrows There is an extra parenthesis after the Start Date. You need to remove that. The formula should look like this:

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

Thanks for catching that. Clearly I wasn’t on my best proofreading behavior that day.

Hi. Has anyone managed to work out how to calculate total number of hours worked between two dates (multiple weeks sometimes) but only including working hours e.g. Mon-Fri, 0900-1700 ?