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

lease-formula

Thank you!

Anet Gambina


#2

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.