MATCH VLOOKUP

[Pippy79]

Hi

I'm looking to see if I can get a vlookup to display a date of collection based of the cert date

So below the cert date is fri, 16sept and i need to know that collection is mon, 19sep at 11:00 so a formula in B2+C2

	A	B	Collection Time
1​	POTENTIAL CERT DATE	COLLECTION DATE	Collection Time
2​	Fri, 16-Sep-22	Mon, 19-Sep-22​	11:00

The cert date is already populated (A2)

this is the key;

	A	B	C
1​	CERT DATE	SHIPMENT	Collection Time
2​	Mon	Wed	11:00
3​	Tue	Thu	11:00
4​	Wed	Fri	11:00
5​	Thu	Mon	11:00
6​	Fri	Mon	11:00
7​	Sat	Mon	11:00
8​	Sun	Tue	11:00

Thanks

 

[Gerald Higgins]

If I understand this correctly, the collection time is ALWAYS 11:00, and therefore the hard part of this is just to work out the correct SHIPMENT date, based on the CERT date, is that correct ?

If yes, then maybe something like this, if CERT date is in A2, this will work out the SHIPMENT date . . .
=A2+2+(WEEKDAY(A2,1)=5)*2+(WEEKDAY(A2,1)=6)

It is then simple to either add the time to the result of this, to get a combined numeric date and time.
Or, if you want a text string, use the TEXT function to give you a text version of the date, and concatenate that with a text version of the time.

 

[Pippy79]

Thanks Gerald! thats spot on