Workday and VLook up

Hello,

This formula works, but it gives me an exact date (my referenced date plus the number of days referenced via the VLOOKUP). I've tried to incorporate the Workdays function without success and wonder if someone else might be able to help me. Here is the formula that works:

=([PCD Draft 1 Start Date]@row + (VLOOKUP([Combined Doc type and doc status]@row, {Combined and New}, 2, false)))

This adds 6 days to my date of 12-02-2022, which correctly gives me a date of 12-08-2022 . But, I'd like it to add 6 Workdays to my PCD Draft 1 Start date.

Thanks in advance!

Find more posts tagged with

Accepted answers

Ryan Sides

@BFuller ok, it looks like since you have 6.5 as the number of days, the workday function doesn't like it. It looks for integers, so you'll need to use the ROUNDUP function...

=WORKDAY([PCD Draft 1 Start Date]@row, ROUNDUP(VLOOKUP([Combined Doc type and doc status]@row, {Combined and New}, 2, false)))

**I'm assuming you'll always want to round up so you're not coming in short on days. But if you want to round up or down, just change it to ROUND.

All comments

Ryan Sides

@BFuller Hi There. If I'm understanding your setup correctly, your Vlookup returns the number of days you want added to the Start Date, but you only want it to add Workdays. If that's right, you can wrap your Vlookup inside of the WORKDAY function.

WORKDAY starts with a date then adds workdays to it.

=WORKDAY([PCD Draft 1 Start Date], VLOOKUP([Combined Doc type and doc status]@row, {Combined and New}, 2, false))

Is that what you were looking for?

BFuller

@Ryan Sides Yes! That is what I am looking to do. I put that formula in my Workday due date column and get Invalid Data Type error. Here's the formula...am I missing something?

=WORKDAY([PCD Draft 1 Start Date]@row, VLOOKUP([Combined Doc type and doc status]@row, {Combined and New}, 2, false))

Many thanks in advance for your expertise!

Ryan Sides

@BFuller That error usually means that the formula is expecting one type (like a date) and getting a different type (like alpha characters or something)

Can you confirm that your PCD Draft 1 Start Date column's type is a Date and the data entered in that row is a date?

BFuller

@Ryan Sides Yes, they are all date columns.

Here is a screenshot in case this helps. Here is the formula I have in that workday due date column:

=WORKDAY([PCD Draft 1 Start Date]@row, VLOOKUP([Combined Doc type and doc status]@row, {Combined and New}, 2, false))

In the Target due date, I have this formula, which provides the date, but it is not workdays. It's a firm number between the PCD Draft 1 Start date plus the # of days to complete: =([PCD Draft 1 Start Date]@row + (VLOOKUP([Combined Doc type and doc status]@row, {Combined and New}, 2, false)))

Ryan Sides

@BFuller Can you send a screenshot of the [Combined Doc type and doc status] column and of the cross sheet reference you created called {Combined and New}?

Ryan Sides

@bfuller are you just wanting to add [# of days to complete] to [PCD Draft 1 Start Date]?

BFuller

@Ryan Sides Yes, but as workdays not actual days. So, this formula works, but I want to incorporate workdays into it: =([PCD Draft 1 Start Date]@row + (VLOOKUP([Combined Doc type and doc status]@row, {Combined and New}, 2, false)))

So, in my example above where the due date was 12-8-2022, it would calculate it without counting weekends or holidays.

Ryan Sides

@BFuller ok, it looks like since you have 6.5 as the number of days, the workday function doesn't like it. It looks for integers, so you'll need to use the ROUNDUP function...

=WORKDAY([PCD Draft 1 Start Date]@row, ROUNDUP(VLOOKUP([Combined Doc type and doc status]@row, {Combined and New}, 2, false)))

**I'm assuming you'll always want to round up so you're not coming in short on days. But if you want to round up or down, just change it to ROUND.

BFuller

Oh my goodness. I love you! THANK YOU SO MUCH!!!!! That worked!!!

Ryan Sides

You’re welcome! Glad you got it working :)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.