CODEWITHSUNDEEP
exceldateexcel-formula
Sterling
Sterling

Reputation: 3

Excel function to create due dates that land on a business day

I'm stuck creating a formula that will calculate days before the end of the month then adjust to make sure it is a business day. For example: 30 days before 6/30/2015 is 5/31/2015 which is a Sunday. I need that to adjust to the Friday before.

I'm working on finding the due dates of a number of documents that are due a certain number of days before another date. For example: documents are due 30 days before the last day of the month. However, the number of days varies and the due date needs to fall on a business day (Monday-Friday). Sometimes it's 30 days, sometimes it's 60 days, sometimes it's 30 calendar days + 5 business days, etc.

I've been able to calculate 30 days + 5 business days with the following formula:

=workday(start_date-30,-5)

Any ideas how to adjust this so that I can just have the due date be 30 calendar days before a certain date but also always be a business day?

Upvotes: 0

Views: 1359

Answers (2)

Michael Gruver
Michael Gruver

Reputation: 11

Using WORKDAY you can use a formula like this:

=WORKDAY(A1+B1+1,-1)

where A1 is your start date and B1 the number of days to add.

Upvotes: 1

Vic F
Vic F

Reputation: 1459

You probably need to write a macro function or maybe some nested IF statements in your cell's formula.

Take a look at http://www.mrexcel.com/forum/excel-questions/481558-round-date-nearest-workday.html

That solution moves forward to the nearest workday, but the principle is sound: just subtract instead of add.

Upvotes: 0

Related Questions