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
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.
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!
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…
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!
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'
)
Jan 10, 2019 08:12 AM
Got it! Thanks a lot!!!
Mar 13, 2019 11:32 AM
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.
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!
Mar 19, 2019 02:08 PM
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>)