UDF for modified workdays

[albasheer]

Hello,

Would someone please help me build a UDF similar to the WORKDAY(start_date,days,holidays) function, but with Fridays and Saturdays as off days instead of Saturdays and Sundays?

If there is a formula solution instead of a UDF, that would also be great.

Thank you.

 

[barry houdini]

You can just use WORKDAY with an "offset", i.e.

=WORKDAY(start_date+1,days,INDEX(holidays+1,0))-1

 

[albasheer]

Thank you, Barry. I was thinking about a UDF because I can modify it to allow for Fridays only as off days. But do you think this can be done using a formula? I started trying to reverse the formula:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(C3&":"&D3)))={1,2,3,4,5,7})) which counts non-Fridays between two days, but I got stuck. My mind is not clear enough today.

 

[barry houdini]

WORKDAY is trickier to modify than NETWORKDAYS but you can do it with a formula like this

=SMALL(IF(ISNUMBER(MATCH(WEEKDAY(A2+ROW(INDIRECT("1:"&B2*10))),{1;2;3;4;5;7},0)),ROW(INDIRECT("1:"&B2*10))),B2)+A2

confirmed with CTRL+SHIFT+ENTER

You can modify the {1;2;3;4;5;7} part to include the days you want

where A2 is start date and B2 is days to add.......it gets more complex if you want to consider holidays...

I'm sure a UDF could do it too, but I'm not your man for that......or alternatively wait for Excel 2010 which has a new WORKDAY.INTL function that can be modified to account for any combination of working days

 

[albasheer]

Thank you very much, barry. The multiplication by ten and then the use of SMALL! I like that.

And thanks for the additional info about the new feature in Excel 2010.