About IF statment

Bino

Hello everyoune, please I need your help with this isuue because I almost gave up to answer it. Please I appreciate your help

I would to apply either IF/IF And statment to the formula.

The logical thinking is:

IF (end date-today()) =0 and the description is not empty Then "completed" But if and description=" " then "no data"

IF (end date-today())<0 and >=-2Then "late"

IF (end date-today())>=-3 and description=" " Then "no data"

IF (start date >today()) Then "not started"

IF we are still in the time frame between start date and end date + the description is not empty Then "In progress"

Kelly Moore

Hello @bashaer

Below is the nested IF formula that I believe you requested but I do not believe it is doing exactly what you intended. I think it is missing some provisions about blank descriptions (see your Late response). Also, what if either Start Date or End Date do not have a date?

=IF(AND([End Date]@row = TODAY(), Description@row <> ""), "Completed", IF([Start Date]@row > TODAY(), "Not Started", IF(OR(AND([End Date]@row = TODAY(), Description@row = ""), AND([End Date]@row < TODAY(-3), Description@row = "")), "No Data", IF(AND([End Date]@row > TODAY(-2), [End Date]@row < TODAY()), "Late", IF(AND([Start Date]@row <= TODAY(), [End Date]@row > TODAY(), Description@row <> ""), "In Progress")))))

Start here and we can begin tweaking your formula

Kelly

Bino

Thanks a lot Kelly, I appreciate your help,

Actually the formula dosen't work! It gives me all status as "no data"😞 Any ideas?! I'm thinking for description would be important in just "no data" status, so in the other status will be ignored.

Regards your Q "what if either Start Date or End Date do not have a date?" the status would be "not started"

Thanks again. @Kelly Moore

Kelly Moore

Hey

Hmmm, the formula was giving different responses to me other than "No Data". I'll check again

To clarify - anytime the description is missing then the status is "No Data", regardless of any dates? Please advise

I'll add the missing Start Date/End Date criteria

Kelly

Bino

Hello again,

"anytime the description is missing then the status is "No Data", regardless of any dates?"

"No data" IF (end date-today())>=-3 and description=" "

So if the task is late more than 3 days and there is no description for the task then the status should show "no data".

And if the task still going on in the time frame and the description is not empty then "In progress".

If the task is late 1 or 2 days then " Late".

If the task is due in the same day and the description is not empty then "completed". But if the description is empty then "no data" even if it is due in the same date, and turns into late after 1 day. after 2 days also late, after 3 days would be no data.

I hope I'm not confusing you again! 😥

Thank you Kelly @Kelly Moore

Kelly Moore

Hey @bashaer

Try this

I rearranged the order of the IFs to make the Description field higher in priority- which means an empty description field will trump other responses. You will need to verify the order of decisions is what you were expecting.

You should be able to copy this formula directly into your sheet

=IF(AND([End Date]@row = TODAY(), Description@row <> ""), "Completed", IF(Description@row = "", "No Data", IF(OR([Start Date]@row > TODAY(),[Start Date]@row="", [End Date]@row=""), "Not Started", IF(AND([End Date]@row > TODAY(-2), [End Date]@row < TODAY()), "Late", IF(AND([Start Date]@row <= TODAY(), [End Date]@row > TODAY(), Description@row <> ""), "In Progress")))))

Does this work for you as expected?

Kelly