Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

can a VLookup include "Exclude roll forward if weekend dates returned"

Can an expert resolve this for me.

I am using a VLookup i.e.:

=IF(ISERROR(VLOOKUP(A2,Nov!D:I,6,0)),"",(VLOOKUP(A2,Nov!D:I,6,0)))

But if the date it returns is a weekend I need it to to be rolled forward to the next working day i.e. Monday

so if the date it returns is 27/9/14 I need it to change to 29/9/14

if the date it returns is 28/9/14 I need it to change to 29/9/14

Is this possible

Thanks
0
Jagwarman
Asked:
Jagwarman
  • 6
  • 4
1 Solution
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Assuming you have Excel 2007 or greater, you can use IFERROR(A1,""), instead of IF(ISERROR(A1),"",A1) - it's quicker.

Here's the answer:

=IFERROR(VLOOKUP(A2,Nov!D:I,6,0)+CHOOSE(WEEKDAY(VLOOKUP(A2,Nov!D:I,6,0),1),1,0,0,0,0,0,2),"")

Open in new window

0
 
JagwarmanAuthor Commented:
wow that was a fast reply. Brilliant I would never have worked that out thanks Phillip
0
 
JagwarmanAuthor Commented:
Very helpfull Expert.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JagwarmanAuthor Commented:
Phillip,

sorry to trouble you again on this one but, when the VLookup does it's work, if the field it looks up is blank it is returning 02/01/1900

I thought that IFERROR would stop that?

Thanks & Regards
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
No, "IFERROR" or "IF(ISERROR" will only return a blank if there is an error.

If column I is nullable, then VLOOKUP will return a zero (instead of a blank value), and you need to check for it:

=IFERROR(if(VLOOKUP(A2,Nov!D:I,6,0)=0,"",VLOOKUP(A2,Nov!D:I,6,0)+CHOOSE(WEEKDAY(VLOOKUP(A2,Nov!D:I,6,0),1),1,0,0,0,0,0,2)),"")

Open in new window

0
 
JagwarmanAuthor Commented:
sorry I have another problem now

The date it is looking up is 17/11/2014 which is a Monday but it is returning 19/11/2014

Would you know why?
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
It isn't for me. Are you sure that there isn't an earlier version of the thing that it is looking up, which has an earlier date?
0
 
JagwarmanAuthor Commented:
this is going to be one of those bizzare things. I have played around with the dates but can't resolve
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Do you want to post your spreadsheet, or is it confidential?
0
 
JagwarmanAuthor Commented:
it was me, sorry and BIG thanks for your help

instead of D:I,6,0   D:I,6,0   D:I,6,0

I somehow put

D:I,6,0   D:I,6,0   D:I,7,0
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now