Excel 2010 - Formula for days overdue HELP!

New member here, just starting a new job which requires a lot of Excel work, so I plan on being here for a long time!! HAHA


So, I'm not the best at Excel and this may be something simple that I'm missing. Onto the problem, I am setting up a file here that I want to do essentially two things.

1) I want the DAYS OVERDUE column to show up from the TODAY'S DATE column to the DATE DUE column. I can figure that portion out, by using the =NETWORKDAYS formula.

2) I want to find a way so that when I input the RETURNED column that the DAYS OVERDUE column will stop increasing when the RETURNED field has been filled in.


I just want to make something simple like this so that my manager can open the file up and see exactly how many days something was overdue but when it gets returned, it shows the final number there of days overdue and does not continue to increase.

Any help please on what formula I need to do for this?


SEE LINK BELOW FOR SCREENSHOT!
\1

Given that you don't want it to continue to count after it has been returned, but keep that figure there, something like this should work I believe:

=IF(G10<>"",G10-D10,IF($B$2>D10,$B$2-D10,""))

Given that:

G10= Return Date
D10= Date Due
B2= Today

Originally Posted by deadpanda

Given that you don't want it to continue to count after it has been returned, but keep that figure there, something like this should work I believe:

=IF(G10<>"",G10-D10,IF($B$2>D10,$B$2-D10,""))

Given that:

G10= Return Date
D10= Date Due
B2= Today

This is looking pretty good, but it appears to keep weekends into the situation. How do I keep weekends out?

Originally Posted by daffodil11

Welcome to the forums!

Please be sure to read the forum rules, so that you stay out of trouble. Help in a thread title is a no-no. Who isn't looking for help? That's not specific at all.

Also, posting pictures of your work isn't very helpful. You're here to ask for help to save you and other people time; don't waste your potential helpers' time by forcing them to recreate your work. If your data is sensitive, de-sensitize it or create something from scratch we can work from.

That said, you're solution would most likely involve a circular reference. You can enable this by going to File-> Options ->Formulas ->Enable Iterative Calculation

Let's make some assumptions:
M15 = today
K16 = due date
K18 = returned date

The answer: =IF(K18<>"",K17,M15-K16)

If the returned date isn't blank, K17 = K17, otherwise show the difference of the due date and today.

My mistake, I apologize for messing up. WOnt happen again, thanks for the head up!!

This is a great formula too, but it appears to keep weekends in there as well?