Help with excel formula please

emjaytx

I am trying to accomplish the following in a new hire training plan:

I have a column for Expected Start Date(D4), Start Date (E4), Actual Completion Date (F4) and Status (G4).

I want the status column (G4) to reflect the data placed in the other cells.

1. If only D4 is populated, then G4 should say "Not Started".
2. If D4 and E4 are populated, then G4 should say "In Progress".
3. If D4, E4, and F4 are populated, and then G4 should say "Complete".

These are all true unless:

1. No data in E4 or F4 and D4 is > 30 days ago. In this case, G4 should say "Not Started-Past Due" OR
2. D4 and E4 are populated. No data in F4 and D4 is > 30 days ago. In this case, G4 should say "In Process-Past Due"
3. Data in D4,E4, and F4, but D4 is > 30 days ago OR if F4>D4. In this case, G4 should say "Completed-Past Due"

I started with THIS, but it's definitely not right. I got very confused with my own request…

=IF(F5>D5,"Completed",IF(ISBLANK(F5),"In Progress",IF(D5<E5,"Past Due, IF(ISBLANK(F5)""NotStarted")))

I'm sorry if this is convoluted. I am definitely no Excel expert to say the least. Including a screenshot.

Kelly Moore

hey @emjaytx

Happy New Year! If you are wanting an excel formula for an excel sheet, you may find better help in an Excel community forum. This is a Smartsheet community, which is a different platform. I've had good luck in prior years with this excel community: https://www.excelforum.com/

Or, are you trying to convert this excel formula into a smartsheet formula?

Kelly

Kelly Moore

hey @emjaytx

Happy New Year! If you are wanting an excel formula for an excel sheet, you may find better help in an Excel community forum. This is a Smartsheet community, which is a different platform. I've had good luck in prior years with this excel community: https://www.excelforum.com/

Or, are you trying to convert this excel formula into a smartsheet formula?

Kelly

emjaytx

https://community.smartsheet.com/discussion/comment/466902#Comment_466902

Thank you so much! I will check into that forum.