Help on Formula: automate status based on dates

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

Project Status.png

Find more posts tagged with

Formulas

Comments

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

Status Formula.png

Status Formula 2.png

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

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Status Log Push to Dashboard
I want to push the latest status log sheet entry to the dashboard. I have added working columns for the last date, recent value (0 or 1) and recent status (text field). The recent columns are blank except for the most recent data rows (that part of the sheet is working). I have a project metric sheet template across topics…
Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
Sheet Summary Formula
I am looking to count how many letters we have issued within each month, this is by going by the modified date, these are the formulas i have tried as i want to start this from August: =COUNTIFS(Modified:Modified, MONTH(@cell ) = 8, Modified:Modified, YEAR(@cell ) = 2025) However, it comes up with an error. Any ideas…