Skip to main content

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!

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

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!!

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



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.

Reply


Terms & Conditions