Help

Need help with a date difference formula with some interesting pieces

Topic Labels: Formulas
Solved
Jump to Solution
596 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Justin_Miller1
4 - Data Explorer
4 - Data Explorer

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
Justin_Miller1
4 - Data Explorer
4 - Data Explorer

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
Justin_Miller1
4 - Data Explorer
4 - Data Explorer

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