Microsoft Project Formula to Smartsheet

John Loushin

Hello,

I am using a formula in Microsoft Project to automate status changes based of date percent. I wanted to see if anyone would be able to decipher the MS Project folder to a Smartsheet formula.

IIf([Current Date]<[Start],"Early",IIf([Current Date]>[Finish] And [% Work Complete]<100,"Late",IIf([% Work Complete]=100,"Done",IIf((DateValue([Current Date])-DateValue([Start]))/(0.001+(DateValue([Finish])-DateValue([Start])))>[% Work Complete]/100+0.01,"Behind","On Track"))))

Thanks in advance!

Paul Newcome

Try this:

=IF([% Work Complete]@row = 1, "Done", IF(Start@row> TODAY(), "Early", IF(Finish@row< TODAY(), "Late", IF((TODAY() - Start@row) / (Finish@row - Start@row)< [% Work Complete]@row, "Behind", "On Track"))))

Paul Newcome

Try this:

=IF([% Work Complete]@row = 1, "Done", IF(Start@row> TODAY(), "Early", IF(Finish@row< TODAY(), "Late", IF((TODAY() - Start@row) / (Finish@row - Start@row)< [% Work Complete]@row, "Behind", "On Track"))))

John Loushin

Thank you Paul! I messed around and went a little more detailed. I changed the names to my column names. I used this below:

=IF(ISBLANK([End Date]@row), "Not Started", IF(TODAY() < [Begin Date]@row, "In Progress", IF(AND(TODAY() > [End Date]@row, [Percentage Complete]@row < 1), "At Risk", IF([Percentage Complete]@row = 1, "Complete", IF(((TODAY()) - ([Begin Date]@row)) / (0.001 + (([End Date]@row) - ([Begin Date]@row))) > [Percentage Complete]@row / 1 + 0.01, "On Watch", "In Progress")))))

Paul Newcome

Happy to help. 👍️