# Recalculate expiry date based on two factors

#### michelleha

##### New Member
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!!

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.

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!

That all sounds good! Thanks for the feedback.

Replies
2
Views
281
Replies
4
Views
279
Replies
3
Views
141
Replies
3
Views
445
Replies
4
Views
144

1,203,252
Messages
6,054,385
Members
444,721
Latest member
BAFRA77

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back