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
May 19, 2019 07:58 PM
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?
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?
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:
SUM()
So…I haven’t given you a complete answer to your problem, but hopefully this gives you enough to correct what you have
JB
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?
Dec 02, 2019 11:40 AM
@Justin_Barrett - nice that we ended up in a similar place! :slightly_smiling_face:
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:
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.
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')
Jan 21, 2021 09:19 PM
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!
Jan 21, 2021 10:07 PM
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?