Hi everyone, I'm trying to do three things within one cell based on data in several other cells.
1. calculate the months remaining between two dates. However...
2. if the Project End date is past then check the General status column, if column does not say "Complete" the mark it as "Overdue"
3. if the Project End date is past then check the general status column, if column says "Complete" then mark it as "Complete"
See below for a visual...
1. calculate the months remaining between two dates. However...
2. if the Project End date is past then check the General status column, if column does not say "Complete" the mark it as "Overdue"
3. if the Project End date is past then check the general status column, if column says "Complete" then mark it as "Complete"
See below for a visual...
| Overdue dates.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Project Start Date (yyyy-mm) | Project End Date (yyyy-mm) | Mths Remaining | % Through Schedule | % Work Completed | General Status | |||
| 2 | July 1, 2019 | August 1, 2020 | 13 | 127% | Finishing | I would like C2 to show "overdue" since the General Status is not "complete" | |||
| 3 | October 1, 2020 | February 28, 2021 | 4 | 32% | I would like C3 to show the months remaining if it is not overdue or complete | ||||
| 4 | October 21, 2020 | December 1, 2020 | 1 | 67% | 85% | I would like C4 to show the months remaining if it is not overdue or complete | |||
| 5 | August 2, 2013 | October 7, 2019 | 74 | 118% | 100% | Complete | I would like C5 to show "Complete" since the General Status is "complete" | ||
Sheet1 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2:C5 | C2 | =DATEDIF(A2,B2,"m") |
| D2:D5 | D2 | =((DATEDIF(A2,TODAY(),"d")+1)/(DATEDIF(A2,B2,"d")+1)) |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| F2:F5 | List | ='X:\Working\Market-Plant-Environmental\Tracking Sheets\[Copy of edits Environmental Inf. Workload Tracking.xlsx]Lists'!#REF! |
