Help

Need help with a date difference formula with some interesting pieces

Topic Labels: Formulas
Solved
Jump to Solution
106 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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!

1 Solution

Accepted Solutions

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)

See Solution in Thread

2 Replies 2

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"
)
Labels