Help

If formula depending on start date

Solved
Jump to Solution
342 2
cancel
Showing results for 
Search instead for 
Did you mean: 
SquishTraining
4 - Data Explorer
4 - Data Explorer

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!

1 Solution

Accepted Solutions
John_B2
6 - Interface Innovator
6 - Interface Innovator

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

See Solution in Thread

2 Replies 2
John_B2
6 - Interface Innovator
6 - Interface Innovator

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"),
"")
)
 
SquishTraining
4 - Data Explorer
4 - Data Explorer

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!