Hello,
Every month I pull a report of status on completed e-learnings compared to the deadline set for completing it. I am trying to make a template where I can paste the RAW data each time to save as many steps as possible to make it a useful report for my stakeholders.
I have a file with 3 kolumns as showed below. (In the table below is how I want it to come out.)
In the fourth column (status) I want either "Overdue" "Completed On Time" or "Still Time to Complete".
Completed On Time works great, except for when it is completed on the same day as the deadline. In those cases I get Overdue instead of Completed On Time. Formula marked in red below.
Overdue works great when its completed but after deadline. When it is not completed the cell is blank and I don't know how to handle that. I replaced the blanks with text "Not Completed" and that worked, but I would like to cut that step and create a formula that understands what it means if the cell is blank. Marked in green below.
Finally, Still time to Complete does not work at all. I have a cell at the top of the sheet with today's date, but I cant get it to funktion. In this example today´s date is 2023-09-23. That is not even a part of my formula yet, can I incorporate it?
I have tried the IFS formula as written below.
=IFS(M2>L2;"Compleated On Time";M2=L2;"ICompleated On Time";L2="Not Completed";"Not Completed";M2<L2;"Overdue")
| Start Date K2 | Completion Date L2 | Deadline (K2 + 30 days) M2 | Satus |
| 2023-06-21 | 2023-06-21 | 2023-07-21 | Completed On Time |
| 2023-06-21 | 2023-08-16 | 2023-07-21 | Overdue |
| 2023-06-21 | 2023-07-21 | 2023-07-21 | Completed On Time |
| 2023-06-21 | Not completed | 2023-07-21 | Overdue |
| 2023-09-21 | Not completed | 2023-10-21 | Tome to Complete |
Thank you so much fot taking your time to read and reply to this.
