hi everyone,
firstly this is my first post so apologies if I do anything wrong, I did read the rules so will try my best!
I am creating a new case tracker for the midwives I work with, I hope you can see the image below, unfortunately due to the data contained in the worksheet I wouldn't be allowed to attach a copy.
there are several reports they need to write by certain deadlines (e.g. draft at 4 months, final version at 6 months.)
I created the formula to pre-fill all these dates correctly, but obviously this would be the 'target' date, which is what I have called these columns. I have then added another column to the right, which will calculate if this is 'not due' or 'overdue'.
this works perfectly and is really helpful, however the cell next to this overdue/not due column would then be where the midwife would be required to input the date they actually submitted the report.
I wondered if there was a way that I could add to the formula (or maybe I need a different formula all together?) to somehow get the 'overdue/not due' to either clear itself/ change to done once a date has been inputted in the 'actual date completed' column? the main issue is the visual display of 'overdue' for most of the reports when they have obviously been done since.
My current formula is =IF(ISBLANK(V5),"",IF(V5<TODAY(),"Overdue","Not due"))
My first thought was I could just add another IF(X6<=TODAY(), "complete") or something to that effect but I am just not good enough at excel to know how to make it work!
Thanks in advance everyone I really hope this makes sense as I am about to have a stroke with the stress this is causing my brain!
Excel Help.PNG
Bookmarks