Calendar days (not working days) calculation, date falls on weekend, move to next business day?

[scottiesq]

I am able to calculate dates in advanced based on a number of days ahead. However, I need the formula to move the given date to the next business day if it falls on a weekend or holiday.

Thus, it is a kind of a hybrid formula, Calendar date plus certain amount of days (e.g. 5 days) but falls on a Saturday, so need it moved to the next Monday. I am familiar with the holidays date range feature, but do not know how to make thes last part work (meaning moving to the next business day).

Thank you for your help.

 

[Ver101]

I am able to calculate dates in advanced based on a number of days ahead. However, I need the formula to move the given date to the next business day if it falls on a weekend or holiday.

Thus, it is a kind of a hybrid formula, Calendar date plus certain amount of days (e.g. 5 days) but falls on a Saturday, so need it moved to the next Monday. I am familiar with the holidays date range feature, but do not know how to make thes last part work (meaning moving to the next business day).

Thank you for your help.

You can use the weekday function

 

[barry houdini]

Hello scottiesq, try using WORKDAY function, e.g. to add 5 days to A1 but move the result to next working day if that date is a weekend

=WORKDAY(A1+5-1,1)

You can also add a holiday range

 

[Robert Mika]

Hi scottiesq and wlecome to the board,
In example below the Holidays day are a Named Range from column G.
in normal circumstances the result should be Monday 31 Jan 11 but becouse I have two holidays days it moved taht day to 2 Feb.
You can add as many days as you like

Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">1/1/2011</td><td style="text-align: right;;">21</td><td style="text-align: right;;">02 Feb 11 Wednesday</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1/18/2011</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1/20/2011</td></tr></tbody></table>

Sheet2​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">C1</th><td style="text-align:left">=WORKDAY(A1,B1,Hol)</td></tr></tbody></table></td></tr></tbody></table>
<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Workbook Defined Names<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">Hol</th><td style="text-align:left">=Sheet2!$G$1:$G$10</td></tr></tbody></table></td></tr></tbody></table>

 

[scottiesq]

Thank you all for the quick replies. I really appreciate it. I am primarily using Open Office Calc (if you recommend not doing so, please feel free to tell me), so I am not sure how some of your replies work (meaning I am looking for the equivalent but have not yet found it).

@barry , I am not sure exactly what this is trying to tell me. =WORKDAY(A1+5-1,1). A1 is the cell, I get that. 5 -1 seems like the number of days to count, but what is the point of subtracting 1 from 5? Why not just write 4 (and what would that signify in any event)?

I am somewhat familiar with the Workday function. right now, that is what I am using for certain formulas. But, that is different from when I need to use the calendar days (as opposed to business day formula) which puts me into the weekend date issue.

@ver101, it seems the weekday formula is what I need to use, but I am lost as to how to write it. Can I use an "IF" formula or something else to tell it to move it to the next workday if it falls on a weekend?

Again, thanks to all for your help. I am transitioning from law to business, and am starting to use Google Spreadsheet, Open Office Calc, and Excel. Now, I wish I would have a taken a class in school.

 

[barry houdini]

Hello scottiesq, the formula I suggested does what you want I think (in Excel at least), i.e.

=WORKDAY(A1+5-1,1)

Yes, I could have used +4 rather than +5-1 but I wanted to include the 5 as that was the number of days to add, so it's generic in that respect, perhaps it would be better to suggest

=WORKDAY(A1+B1-1,1)

where B1 contains the number of days to add

Note this isn't adding 5 workdays, it's adding 5 calendar days to the date then subtracting 1 and adding one workday. That has the effect of moving it to the next working day if A1+5 is a weekend date.

For example, if A1 is a Monday then A1+5 will give you a Saturday. In the above formula A1+5 gives the Saturday, -1 will give you the Friday, then WORKDAY adds 1 workday to that...to give you the Monday as required.

Conversely if A1+5 is already a weekday the above formula returns that date unaltered, e.g. if A1 is a Friday then A1+5 gives the following Wednesday...so you want that date - the above formula uses A1+5 to get the Wednesday, -1 gives you the Tuesday...and WORKDAY adds a working day to give you.....the Wednesday as required.

You could also use WEEKDAY function as Ver101 suggests. These three formulas should give the same results

=WORKDAY(A1+B1-1,1)

and

=A1+B1+LOOKUP(WEEKDAY(A1+B1),{1,2,7;1,0,2})

...or with simpler IF functions

=A1+B1+IF(WEEKDAY(A1+B1)=7,2,IF(WEEKDAY(A1+B1)=1,1))

 

[scottiesq]

Big thank you! Have a great Thanksgiving!

 

[dblanton03]

This function you posted is pure genius! I have been looking for the solution to my issue for three days and wah-lah! You have solved everything!

Thank you!!

=A1+B1+IF(WEEKDAY(A1+B1)=7,2,IF(WEEKDAY(A1+B1)=1,1))[/QUOTE]

 

[AdonisBlue]

Hi I was wondering if i can add another question:

This is my formula: =IF(ISBLANK(H10)=FALSE;(H10+45);"") but i am struggeling to ensure that the 45 calender day does not fall on a weekend. Therefor if 45 days is Sunday or Saturday, the date reflected must move to Monday . I tried to work on WEEKDAY but not get the formula right.

Anyone can help please.

 

[barry houdini]

Try this version

=IF(H10="";"";WORKDAY(H10+45-1;1))

That should do what you need