Skip to main content
Solved

DateTime_Diff formula with three different possibilities

  • September 24, 2021
  • 4 replies
  • 29 views
T

Hello-

I’d like to create a formula that allows me to track expiration dates status (three possibilities):

  1. Due soon, but not within four weeks (blank value in table);
  2. Due within four weeks (“Yes”);
  3. Past due (“Late”)

I’ve been able to create this with the first two status items, but not the third (“Late”). Here’s what I have:

IF(
AND(
DATETIME_DIFF({Expiration}, NOW(), “days”) < 28,
DATETIME_DIFF({Expiration}, NOW(), “days”) > 0
), “Yes”
)

Thanks!

Best answer by Rebecca_Elam

Hmm. This is giving me an error, but I’ll see if I can tweak it. Thank you!!


agh it has :face_with_symbols_over_mouth: smart quotes in it. you need to retype all the quotes. see if this works, if not just literally delete the quotes and retype them.

IF(AND(DATETIME_DIFF({Expiration}, NOW(),‘days’)<28,
DATETIME_DIFF({Expiration}, NOW(), ‘days’)>0),‘YES’, IF(DATETIME_DIFF({Expiration},NOW(),‘days’)<0,‘LATE’))

4 replies

R
Forum|alt.badge.img+4
  • Rebecca_Elam
  • Inspiring
  • September 25, 2021

you need something that says like

IF(AND(

DATETIME_DIFF({Expiration}, NOW(),‘days’)<28,

DATETIME_DIFF({Expiration}, NOW(), ‘days’)>0),‘YES’, IF(DATETIME_DIFF({Expiration},NOW(),‘days’)<0,‘LATE’))

T
T
  • TVR80
  • Author
  • New Participant
  • October 1, 2021

you need something that says like

IF(AND(

DATETIME_DIFF({Expiration}, NOW(),‘days’)<28,

DATETIME_DIFF({Expiration}, NOW(), ‘days’)>0),‘YES’, IF(DATETIME_DIFF({Expiration},NOW(),‘days’)<0,‘LATE’))


Hmm. This is giving me an error, but I’ll see if I can tweak it. Thank you!!

R
Forum|alt.badge.img+4
  • Rebecca_Elam
  • Inspiring
  • Answer
  • October 2, 2021

Hmm. This is giving me an error, but I’ll see if I can tweak it. Thank you!!


agh it has :face_with_symbols_over_mouth: smart quotes in it. you need to retype all the quotes. see if this works, if not just literally delete the quotes and retype them.

IF(AND(DATETIME_DIFF({Expiration}, NOW(),‘days’)<28,
DATETIME_DIFF({Expiration}, NOW(), ‘days’)>0),‘YES’, IF(DATETIME_DIFF({Expiration},NOW(),‘days’)<0,‘LATE’))

T
T
  • TVR80
  • Author
  • New Participant
  • October 3, 2021

agh it has :face_with_symbols_over_mouth: smart quotes in it. you need to retype all the quotes. see if this works, if not just literally delete the quotes and retype them.

IF(AND(DATETIME_DIFF({Expiration}, NOW(),‘days’)<28,
DATETIME_DIFF({Expiration}, NOW(), ‘days’)>0),‘YES’, IF(DATETIME_DIFF({Expiration},NOW(),‘days’)<0,‘LATE’))


Worked like a charm. Thank you thank you thank you.

Terms & ConditionsAccessibility statement