Unsure where WORKDAY fits in with a VLOOOKUP to another sheet

jmhoward

A sheet has task start and end dates for various product tasks.

The products and tasks are listed in a separate project task lookup sheet.

The Task End Date is calculated as x days from the Task Start Date, depending on what the task and product is. Both columns are Date columns.

This formula works, but it doesn't exclude weekends or holidays:

=[Task Start Date]@row + VLOOKUP([Product Version]@row + Task@row, {ConcatProdTaskLookup}, 2, false)

I want to add the WORKDAY function to at least skip weekends when calculating the Task Due Date.

But when I try the formula below, I get an #INCORRECT ARGUMENT SET error:

=WORKDAY([Task Start Date]@row + VLOOKUP([Product Version]@row + Task@row, {ConcatProdTaskLookup}, 2, false))

I'm sure I'm placing WORKDAY in the wrong place, but I'm also wondering that's the correct approach. I haven't even tried to figure out how the holidays fit in, but that would be a bonus.

Thank you for any advice!

Scott Orsey

Hi @jmhoward , I think this will work for you:

=WORKDAY([Task Start Date]@row, VLOOKUP([Product Version]@row + Task@row, {ConcatProdTaskLookup}, 2, false))

A few notes: You may want to use INDEX(MATCH()) construct instead of VLOOKUP(). There are a lot of discussions in Community about the merits of this approach.

You may also want to consider the INDEX(COLLECT()) construct which is an easier way to lookup when you need more than one condition to be met. It doesn't require creating a key ([Product Version]@row + Task@row), Again... search in Community and you'll find out how this works.

But... your formula (with the edits I've suggested) is perfectly legit too.

For holidays, you just need a date column listing holidays:

=WORKDAY([Task Start Date]@row, VLOOKUP([Product Version]@row + Task@row, {ConcatProdTaskLookup}, 2, false),{Holidays})

Good luck and be well!

Scott Orsey

Hi @jmhoward , I think this will work for you:

=WORKDAY([Task Start Date]@row, VLOOKUP([Product Version]@row + Task@row, {ConcatProdTaskLookup}, 2, false))

A few notes: You may want to use INDEX(MATCH()) construct instead of VLOOKUP(). There are a lot of discussions in Community about the merits of this approach.

You may also want to consider the INDEX(COLLECT()) construct which is an easier way to lookup when you need more than one condition to be met. It doesn't require creating a key ([Product Version]@row + Task@row), Again... search in Community and you'll find out how this works.

But... your formula (with the edits I've suggested) is perfectly legit too.

For holidays, you just need a date column listing holidays:

=WORKDAY([Task Start Date]@row, VLOOKUP([Product Version]@row + Task@row, {ConcatProdTaskLookup}, 2, false),{Holidays})

Good luck and be well!

jmhoward

Thank you again @Scott Orsey! You have been very helpful as I navigate a bear of a project!

I tried

=WORKDAY([Task Start Date]@row, VLOOKUP([Product Version]@row + Task@row, {ConcatProdTaskLookup}, 2, false))

It returned an #INVALID DATA TYPE error.

I confirmed that the Task End Date Column is a Date column.

I looked at INDEX COLLECT, but truthfully, I couldn't quite get it to do what I needed it to. I was thrilled when I finally got the VLOOKUP formula to work, so I didn't go back to INDEX COLLECT.

Scott Orsey

@jmhoward , I'm having one of those days where I'm just off a bit. I can't see what's wrong with your formula. So many things to check... Could you look at just the VLOOKUP to ensure that it is returning the correct value for each row? If that's working, I'm a bit at a loss. You could also try:

=WORKDAY([Task Start Date]@row, 5)

To see if it will work with a hard-coded value. One or the other is going to reveal itself! Good Luck!