Help on Formula: if function
Please need your help. I'm new to formulas and I've been trying to figure out the right formula for my project status and progress report but I kept getting errors. I have tried the below formula in excel and it worked but not in smartsheet (the smartsheet formula below is another trial I did that did not work).
Excel: =IF(H3>=F3,"Completed",IF(AND(G3=" ",H3=" "),"Active","Not Due"))
Smartsheet: =IF(AND([Actual End Date]1 = >[Planned End Date]1, "Completed", IF(ISBLANK([Actual End Date]1), "Active", IF([Actual Start Date]1 < [Planned Start Date]1, "Not Due"))))
What I would like to determine in this report is the Status of the entire project based on the subtasks. So if any of the children have an actual end date then the project is Complete but if any of the children have blanks on the actual start date then it should be marked as Not Started and Active if the actual start date is filled but should be equal to the current date.
The Progress column should be determined based on the planned dates. So if the actual end date is less than or equal to the planned end date, then it should be marked as On-time. Delayed if the actual end date is greater than the planned end date and Not Due if planned start date is in the future so actual start date cell must be blank.
Many thanks in advance for the support,
Help Article Resources
Check out the Formula Handbook template!