IFS formula many conditions

Copper Contributor



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 K2Completion Date L2Deadline (K2 + 30 days) M2Satus
2023-06-212023-06-212023-07-21Completed On Time
2023-06-212023-07-212023-07-21Completed On Time
2023-06-21Not completed2023-07-21Overdue
2023-09-21Not completed2023-10-21Tome to Complete


Thank you so much fot taking your time to read and reply to this.


2 Replies


after many hours of juggling, here are the updated formulas

=IF(L2="Not completed"; IF(TODAY()>M2; "Overdue"; IF(AND(M2<TODAY(); L2>M2); "Overdue"; "Still Time to Complete")); IF(L2>M2; "Overdue"; IF(L2<=M2; "Completed On Time"; "Still Time to Complete")))


Start Date K2Completion Date L2Deadline (K2 + 30 days) M2Satus
21/06/202321/06/202321/07/2023Completed On Time
21/06/202321/07/202321/07/2023Completed On Time
21/06/2023Not completed21/07/2023Overdue
21/09/2023Not completed21/10/2023Still Time to Complete