Recalculate expiry date based on two factors

[michelleha]

Using excel 2013 (2010 at work) I have the following...

	D	F	O	P	Q	S	T
2	Date Received	Date Due	Delay required	New Due Date	Notify Manager	Date Closed	Days Overdue
3	11/12/2013	=IF(ISBLANK(D3),"",D3+14) 25/12/2013	N/A	N/A	N/A	26/12/2013	=DATEDIF(F3,S3,"D") 1
4	12/12/2013	=IF(ISBLANK(D4),"",D4+14) 26/12/2013	Yes	=IF(ISBLANK(O4),"",F4+7) 02/01/2014	=IF(F4=TODAY(),HYPERLINK("mailto:?subject="&SetUp!$B$25&"&body=" &SUBSTITUTE(SUBSTITUTE(SetUp!$E$26,"$",J6),"@",B6), "Notify Manager"),"") Notify Manager		??
5	12/12/2013	=IF(ISBLANK(D5),"",D5+14) 26/12/2013	Yes	=IF(ISBLANK(O5),"",F5+7) 02/01/2014	=IF(F5=TODAY(),HYPERLINK("mailto:?subject="&SetUp!$B$25&"&body=" &SUBSTITUTE(SUBSTITUTE(SetUp!$E$26,"$",J6),"@",B6), "Notify Manager"),"") Notify Manager	05/01/2014	??

<tbody>
</tbody>

Explanation:

Row 4 - Date Received is entered in D, this calculate the required due date in F. If there is going to be a delay in response then "yes" is selected in 0, which calculates the New Due Date in P.

Here is were I get stuck...

1. Q "Notify manager" works when Today=F but how do I get it to take into consideration if a new due date is calculated in P?
2. When the "Notify Manager" is active Admin personal go in and send the email off... but how do I take into consideration weekends.. I want a => function I think...? Thoughts?
3. T "Days Overdue" considers F & S but how do I get it to take into consideration if a new due date is calculated in P?

Thanks heaps!!

 

[StephenCrump]

First observation: It's good practice not to have hard-coded numbers like 14 and 7 scattered throughout your spreadsheet. What happens if in future the extension period is changed from 7 days to 10 days, say? If you try a global replace of 10 for 7, you'll make all sorts of unintended changes to your spreadsheet.

Instead, it's better to set your parameters once, e.g. set $A$1=14 and $A$2 = 7, and refer to these cells in all your formulae. You can also give these cells meaningful range names e.g. ProcessingTime, and ExtensionTime.

I suggest then that you only need one column for Due Date. Formula in F3, for example, can be

=IF(ISBLANK(D3),"",D3 + ProcessingTime + If(O3="YES",ExtensionTime,0))

Finally, yes, if you test F4<=TODAY() it will identify all orders due today or on previous days (so if today is the first day back after a weekend or public holiday, it will include orders due on those dates and now overdue). But presumably you'll want to turn this off if the DateClosed is non-blank, i.e. you don't want "Notify Manager" for all historical orders, filled and unfilled.

 

[michelleha]

Very good point - I am just about to show this spreadsheet to my "wizz" brother-in-law to pick up any flaws of that nature... So taking your advice I have added parameters in my "setup" tab and using the suggested formula - we have lift off!

I have added a conditional format so when "Notify Manager" action has been completed the next cell =yes it will strike through "Notify Manager"..

Thanks so much for your help!

 

[StephenCrump]

That all sounds good! Thanks for the feedback.