Unsure where WORKDAY fits in with a VLOOOKUP to another sheet
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!
Best Answer
-
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!
If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!
Answers
-
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!
If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!
-
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.
-
@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!
If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!