Help on Formula: automate status based on dates

Grace

Hi all,

I'm pretty new with formulas and I wanted to automate the status of each projects based on dates. I have columns Status and Progress. The status column have a selection of: Completed, Active and Not Started and the progress column have: On-time, Delayed and Not Due.

On the screenshot below, the columns Status and Progress were manually calculated and some are incorrect. What formulas do you suggest that I use to get the correct status?

I have tried something like the below in Excel but it's incorrect.

=IF(G3>=E3,"Complete","Active",if(isblank(G3),"Not Started"))

 

Many thanks in advance for your help,

Grace 

Mike Wilday

Hi Grace, 

One peculiar thing about Smartsheet is that it doesn't refer to columns as letters as in Google Sheets or Microsoft Excel. You need to reference the column titles. 

=IF([Actual End Date]3 > [Project End Date]3... like that. You have to use brackets if your column title ends in a number or has white spaces in it. 

I can't tell which columns you are referencing. Could you describe in detail what you are trying to accomplish with the IF formula you created? It looks like you're comparing two dates to determine if the project is complete... but there is an issue with your IF statement, because IF G3 > E3 then you will say complete, but then you add the else, "Active" which will make everything say active if the first statement isn't accurate. Try this: 

=IF([Column G title]3>=[Column E Title]3,"Complete",if(isblank([Column G Title]3),"Not Started", "Active"))

Replace with your actual titles.  

Grace

Hi Mike,

Apologies for the confusion. The formula I provided was from Excel that's why. I have recreated it in Smartsheet and used the below formula (screenshots attached).

=IF([Actual End Date]:[Actual End Date], >=[Planned End Date]:[Planned End Date], "Complete", IF(ISBLANK([Actual End Date]:[Actual End Date]), "Not Started", "Active"))

In the attached example, I wanted to get "Active" in the Status column, "Delayed" in the Progress column and "Not started" (Status column), "Not due" (Progress column) for all the blanks. Is this possible?

Kind regards,

Grace

Mike Wilday

It looks like your trying to determine a staus of the entire project based on the subtasks. Is that correct? So if ALL of the children have an actual end date, then the project is complete, And if any of the children are blank on the actual end date you'll get "Not Started", and if neither of those are true then it will declare it as active?  Is that correct?

Grace

Hi Mike,

Yes, I would like to determine the status of the entire project based on the subtasks. However, 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.

The Progress column will 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 actual is greater than the planned end date and Not Due if it's in the future.

Really happy for your support on this Mike. Highly appreciated!

Kind regards,

Grace

Grace

Hi Mike,

Apologies for asking you about this again but I just wanted to know if the information I provided is clear?

Many thanks in advance for your help,

Grace

Mike Wilday

Sorry, Grace! I was on vacation last week and had no internet access. Just to get this straight... each child row's status should check to see if the actual project is complete? And post that in the child row's status field? Or are you wanting to post that in the summary of the parent task? I think I am getting your requests intermingled and confused.

If you want it for each child row you can put this formula in each child row... paste it into one child row and adjust the row number from 23 to whatever row it is in and then use the little square in the bottom right corner of the cell to drag the formula down for the rest of the children. 

=IF(ISDATE([Actual End Date]23), "Complete", IF(ISBLANK([Actual Start Date]23), "Not Started", IF(ISDATE([Actual Start Date]23), "Started")))

For the status row, based on row 23.