Re: Calculate Difference between Two Dates
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
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.
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…
