workday function

[sanjayvnaik]

I want to use workday function in excel/open office
I have only a start date and no end date
I need to add say 6 working days,this working days include saturday and excludes sundays and holidays.
how i do this

[barry houdini]

Hello sanjayvnail, welcome to MrExcel,

Currently in Excel there is no way to modify WORKDAY function itself to include Saturdays (although Excel 2010 should have WORKDAY.INTL function which will allow you to do that).

If you have start date in A2 and postive number of days to add in B2 with holiday dates listed in H2:H10 then you can use this workaround

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

confirmed with CTRL+SHIFT+ENTER

[sijpie]

Excel does not know about holidays, so you would need to build a table with the holidays and check if a date falls within the table.
=WEEKDAY(date) will give you a number 1 through 7, which normally is 1 for sunday and 7 for saturday. (It may depend on the regional settings, I am not sure)

[sanjayvnaik]

i pasted it directly in open office
and filled data in B2, a2, anfd h2 to h10, but it is not working.

---------------------------------

[sanjayvnaik]

how do i go ahead with this

[barry houdini]

The formula I suggested works in Excel, I don't use open office so I don't know whether all the functions are compatible..........

[sanjayvnaik]

the below formula is working, now i need to add holidays to this, how do i do this


If you have a date in A1 and a number of workdays to add in B1...

=INT(B1/6)*7+A1+MOD(B1,6)+INT((WEEKDAY(INT(B1/6)*7+A1)-2+MOD(B1,6))/6

[barry houdini]

It's not possible to amend that formula to cater for holidays also, that's why I suggested the other version, i.e.

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

This is an "array formula" which means you have to use the key combination CTRL+SHIFT+ENTER

To do that select the cell with formula, press F2 then hold down CTRL and SHIFT keys and press ENTER. If you do that correctly then curly braces like { and } will appear around the formula in the formula bar and the formula should work as expected.......

[sanjayvnaik]

thanks for the reply but it is not working , nothing is getting pasted in formula bar


To do that select the cell with formula, press F2 then hold down CTRL and SHIFT keys and press ENTER. If you do that correctly then curly braces like { and } will appear around the formula in the formula bar and the formula should work as expected.......[/QUOTE]

[barry houdini]

Are you doing this in Excel?

Nothing actually gets pasted in the formula bar......

Let's assume you have my suggested formula already pasted into C2 (if not then paste into that cell)

Now select cell C2 and follow the instructions as before, i.e. press F2 key then hold down CTRL and SHIFT keys and press ENTER.

Now in the formula bar the formula should look like this

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