MATCH VLOOKUP

Pippy79

Board Regular
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

Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Gerald Higgins

Well-known Member
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

Board Regular
Thanks Gerald! thats spot on

Replies
2
Views
331
Replies
3
Views
210
Replies
11
Views
574
Replies
15
Views
122
Replies
6
Views
120

1,181,427
Messages
5,929,827
Members
436,697
Latest member
sunnypl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back