# Calculate Difference between Two Dates

#1

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

#2

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.