Jagwarman
asked on
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
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
I am using a VLookup i.e.:
=IF(ISERROR(VLOOKUP(A2,Nov
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Very helpfull Expert.
ASKER
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
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
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:
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)),"")ASKER
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?
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?
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?
ASKER
this is going to be one of those bizzare things. I have played around with the dates but can't resolve
Do you want to post your spreadsheet, or is it confidential?
ASKER
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
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
ASKER