Skip to main content
Solved

If formula depending on start date

  • January 4, 2023
  • 2 replies
  • 28 views
S
Forum|alt.badge.img+2

Hello,

I'm new to logic formulas and need some guidance. I have an AT to track company orientations. I have three columns- {Start Date}, {Orientation Complete Date}, and {Orientation Status}. I would like to create a formula for {Orientation Status} to show "Complete" when {Orientation Complete Date} is filled in, "Almost Due" when {Start Date} is within 14 days of todays date and {Orientation Complete Date} is empty, and "Overdue" when {Start Date} is after 15 days from todays date and {Orientation Complete Date} is empty.

Any help or a link in the right direction would be appreciated!

Best answer by John_B2

Hi - assuming the 14 days is either before or after the start date try this ...

IF({Orientation Complete Date}, 
"Complete", 
IF({Start Date}, 
IF(ABS(DATETIME_DIFF(NOW(), {Start Date},"Days")) < 15 ,
 "Almost Due", 
"Overdue"),
"")
)
 

    2 replies

    J
    Forum|alt.badge.img+9
    • John_B2
    • Inspiring
    • Answer
    • January 4, 2023

    Hi - assuming the 14 days is either before or after the start date try this ...

    IF({Orientation Complete Date}, 
    "Complete", 
    IF({Start Date}, 
    IF(ABS(DATETIME_DIFF(NOW(), {Start Date},"Days")) < 15 ,
     "Almost Due", 
    "Overdue"),
    "")
    )
     
    S
    S
    Forum|alt.badge.img+2
    • SquishTraining
    • Author
    • New Participant
    • January 4, 2023

    That worked perfectly! Thank you so much. I just couldn't wrap my head around that formula trying to figure it out on my own!

    Terms & ConditionsAccessibility statement