Need help with a date difference formula with some interesting pieces

[Justin_Miller1]

Hello community! Here’s what I’m seeking assistance with:

I have 3 fields total: Length of Service, Service Start Date, and Service End Date (or anticipated)

I would like to write a formula for the Length of Service field that does what I currently have, but takes it one step further. Currently, I have a simple DATETIME_DIFF formula to display in Years the difference between the two dates. However, I would like to use today’s date (I’m guessing the NOW function) IF the Service End Date is in fact an anticipated date in the future.

So if the Service End Date is greater than today, I would like to use today to calculate the time difference, and if the Service End Date is in the past, I’d like to simply calculate the date difference between the two.

Any help is greatly appreciated!

[Justin_Miller1 · Accepted solution]

After some tinkering, I figured it out! Pasted below if anyone was interested (I just over-thought the IF function :stuck_out_tongue: )

IF({Service End Date (or Anticipated)}<NOW(),
DATETIME_DIFF(
{Service Start Date},
{Service End Date (or Anticipated)},
‘years’
) * -1,
DATETIME_DIFF(
{Service Start Date},
NOW(),
‘years’
) * -1)

[Kamille_Parks]

You could also try:

DATETIME_DIFF(
  IF(TODAY() < {Service Start Date}, TODAY(), {Service Start Date}),
  {Service Start Date},
  "years"
)