Need help with a date difference formula with some interesting pieces

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!

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)

You could also try:

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

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.