Predicting Dates not including weekends

[starloverly]

Hello,

I'm working on a spreadsheet where one date is given and others are predicted. This should be simple but the dates cannot be on a weekend and I have some predicted dates that need to default to the Friday before and some that need to default to the Monday after.

As an example:

If there is a date in cell B3, I need the predicted date to be 45 days later, but if that dates falls on a weekend (it won't always) then it needs to default to the Friday before.

Similarly, the next predicted date I need to be 1 day later, but if that date falls on a weekend then it needs to default to the Monday after.

I've tried so many different variations of the date prediction formulas that any help will be appreciated

Thanks!

 

[barry houdini]

Hello starloverly, welcome to MrExcel

Try this for B3+45 - it will give you just B3+45....or previous Friday if that date is Sat or Sun

=WORKDAY(B3+45+1,-1)

and if you have a date in B4 and you want a day later (and Friday, Saturday becomes Monday)

=WORKDAY(B4,1)

 

[starloverly]

Hello starloverly, welcome to MrExcel

Try this for B3+45 - it will give you just B3+45....or previous Friday if that date is Sat or Sun

=WORKDAY(B3+45+1,-1)

and if you have a date in B4 and you want a day later (and Friday, Saturday becomes Monday)

=WORKDAY(B4,1)

Thank you,

Both those formulas work perfectly when the date falls on a saturday.

In the first one, when the date falls on a sunday it defaults to monday and I need it to default back to Friday.

In the second one, when the date falls on a sunday, it defaults to the next tuesday which seems weird.

 

[barry houdini]

In the first one, when the date falls on a sunday it defaults to monday and I need it to default back to Friday.

In the second one, when the date falls on a sunday, it defaults to the next tuesday which seems weird.

I don't see those problems - for example if B3 is Thursday 28th February 2013 then B3+45 will give you Sunday 14th April 2013, so you want the formula to give you the previous Friday, 12th April - that's what my suggested formula does. If B4 is Sunday 24th Feb 2013 then the second formula will give you the next day, Monday 25th.

If you still have problems then can you give some specific examples where you don't get the correct results

 

[starloverly]

I don't see those problems - for example if B3 is Thursday 28th February 2013 then B3+45 will give you Sunday 14th April 2013, so you want the formula to give you the previous Friday, 12th April - that's what my suggested formula does. If B4 is Sunday 24th Feb 2013 then the second formula will give you the next day, Monday 25th.

If you still have problems then can you give some specific examples where you don't get the correct results

Hmm... you're right when I plug in your example. But if the specified date is 2/22/13, and the predicted date is 4/7/13 (a Sunday) it defaults to 4/8 instead of the needed 4/5.

For the second Formula, if the date is 2/22 again, the formula gives me 2/25 which is correct. I think the problem i'm running into with this formula is my own misunderstanding of Excel ><
What would I need to change if I want it to predict 2 workdays into the future, or 4?

For the whole sheet I'm predicting 5 different dates that need to be able to default to a Monday, and 3 dates that need to be able to default to the previous Friday.

Sorry I wasn't explaining myself well, thank you for your patience.

 

[barry houdini]

But if the specified date is 2/22/13, and the predicted date is 4/7/13 (a Sunday) it defaults to 4/8 instead of the needed 4/5.

....but if you are adding 45 days then 45 days after 2/22/13 is 4/8/13 so it doesn't need to be moved at all (if you try 2/21/13 then that gives you 4/7/13 when 45 days are added so the formula correctly then gives the Friday 4/5/13)

Generically if you want to add n days and then get the Friday if date+n is a weekend then use

=WORKDAY(date+n+1,-1)

or for adding n days but getting the Monday if that's a weekend

=WORKDAY(date+n-1,1)

 

[starloverly]

....but if you are adding 45 days then 45 days after 2/22/13 is 4/8/13 so it doesn't need to be moved at all (if you try 2/21/13 then that gives you 4/7/13 when 45 days are added so the formula correctly then gives the Friday 4/5/13)

Generically if you want to add n days and then get the Friday if date+n is a weekend then use

=WORKDAY(date+n+1,-1)

or for adding n days but getting the Monday if that's a weekend

=WORKDAY(date+n-1,1)

Thank you!

Sorry it's taken me awhile to get back.

I figured out the way Excel counts their days is different from the way we do. Once I made that adjustment your formulas worked!

Thank you so much for your help =)