Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

# Need help with a date difference formula with some interesting pieces

Topic Labels: Formulas
Solved
153 2
cancel
Showing results for
Did you mean:
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
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)

2 Replies 2
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)

16 - Uranus

You could also try:

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