Help with excel formula please
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.
- If only D4 is populated, then G4 should say "Not Started".
- If D4 and E4 are populated, then G4 should say "In Progress".
- If D4, E4, and F4 are populated, and then G4 should say "Complete".
These are all true unless:
- No data in E4 or F4 and D4 is > 30 days ago. In this case, G4 should say "Not Started-Past Due" OR
- 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"
- 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.
Best Answer
-
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:
Or, are you trying to convert this excel formula into a smartsheet formula?
Kelly
Answers
-
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:
Or, are you trying to convert this excel formula into a smartsheet formula?
Kelly
-
Thank you so much! I will check into that forum.
Help Article Resources
Categories
Check out the Formula Handbook template!