How to Include Saturday in Formula Workdays

[Ong Eng Thai]

Hi There,
Currently I am using workdays funtion in my office.As we know Saturday & Sunday is not the working day,therfore will not count in Excel because this is default by Excel.
My question is how to Add in Saturday as a working day?
Is there any option to set calendar in Excel?
Appreciate you guys reply...

 

[Ong Eng Thai]

Hi Barry,
Seems like formular is not working....Let me show you example :

Start Date :1/25/10
Duration :5 Days
Holiday :1/26/10
End Date :2/01/10 (If deduct 1 day holiday :1/26/10)

I had try to use yr formular,but the answer is 1/30/10? I guess holiday date is not include.

Best Regards,
ET.

 

[Ong Eng Thai]

Hi Barry,
sorry....I forget to add holiday cell..... Thank you so much...really work.....I wonder how you learn all this formular....Really amazing.....Do you have any other simple formulas because looks like the formulas is quite long....
By the way do you know how to add conditional formatting? Currently I am using this function,but seems like Excel allows only 3 way to add....I thinking to add more than 3....
I hope you can help me on this....

Appreciate your support.

Best Regards,
ET.

 

[barry houdini]

That works OK for me. What holiday range are you using? If you use a different range from H2:H10 then you need to change that in the formula

 

[hemantkshirsagar]

If you want to exclude holidays then you need a different approach, e.g. with holiday dates listed in H2:H10

=SMALL(IF(WEEKDAY(A1+ROW(INDIRECT("1:"&B1*10)))<>1,IF(ISNA(MATCH(A1+ROW(INDIRECT("1:"&B1*10)),H$2:H$10,0)),ROW(INDIRECT("1:"&B1*10)))),B1)+A1

This is an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar


i HAVE the same problem..

I want to include the saturdays and exclude holidays and sunday.

Instead of such long formula, Can't we just have a column which includes all the date that we can avoid in counting? (includes sundays also)

Creation of such column can really help us out for selective avoidance of the holiday.

I want to create a project planning sheet in which activity started on 1-may requires 5 days so what i do now is A2+B1=B2 but i have to consider the holidays, and the work day function has saturday included as holiday, I am not that lucky : ( .

None the less, is there a way out Oh Great Houdini !!

Regards

Hemant Kshirsagar

 

[barry houdini]

If you list all Sundays and holidays in H2:H100 then you can make the formula a little shorter, i.e.

=SMALL(IF(COUNTIF(H$2:H$100,A1+ROW(INDIRECT("1:"&B1*10)))=0,ROW(INDIRECT("1:"&B1*10))),B1)+A1

confirmed with CTRL+SHIFT+ENTER

Note that in the new version of Excel (Excel 2010) coming out this year there's a WORKDAY.ITNL function which will allow you to do this much more easily