updating business day to calendar day values...

sadams1

Board Regular
Joined
Aug 19, 2006
Messages
216
finally crying "uncle!" on this...after trying advanced filters, vlookups & various other avenues that didn't work, all I'll like to do is something seemingly simple...search a column of business dates to match in a column of calendar dates then paste the value from the business to calendar in the next column. This is for market research...here's a 2 week table:

am I making this too difficult or am I approaching it wrong?


All_Dates_Business Close
03/20/20​
19,174
03/19/20​
20,087
03/18/20​
19,899
03/17/20​
21,237
03/16/20​
20,189
03/13/20​
23,186
03/12/20​
21,201
03/11/20​
23,553
03/10/20​
25,018
03/09/20​
23,851
All_Dates_Calendar
03/20/20​
match this value in the "calendar" range to value in "business" range THEN paste value from "business close" into this cell
03/19/20​
match this value in the "calendar" range to value in "business" range THEN paste value from "business close" into this cell
03/18/20​
match this value in the "calendar" range to value in "business" range THEN paste value from "business close" into this cell
03/17/20​
match this value in the "calendar" range to value in "business" range THEN paste value from "business close" into this cell
03/16/20​
match this value in the "calendar" range to value in "business" range THEN paste value from "business close" into this cell
03/15/20​
match this value in the "calendar" range to value in "business" range THEN paste value from "business close" into this cell
03/14/20​
match this value in the "calendar" range to value in "business" range THEN paste value from "business close" into this cell
03/13/20​
match this value in the "calendar" range to value in "business" range THEN paste value from "business close" into this cell
03/12/20​
match this value in the "calendar" range to value in "business" range THEN paste value from "business close" into this cell
03/11/20​
match this value in the "calendar" range to value in "business" range THEN paste value from "business close" into this cell
03/10/20​
match this value in the "calendar" range to value in "business" range THEN paste value from "business close" into this cell
03/09/20​
match this value in the "calendar" range to value in "business" range THEN paste value from "business close" into this cell
03/08/20​
match this value in the "calendar" range to value in "business" range THEN paste value from "business close" into this cell
03/07/20​
match this value in the "calendar" range to value in "business" range THEN paste value from "business close" into this cell
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Select a cell and make one of your business days equal to one of your calendar days (eg =A1=D1) If the value you get is false, then you may have a Data type issue (text vs Date). You may have trim issues (spaces on either side of your dates. Did you copy your business day information off of a website?. One of your dates may be hiding a fractional day (eg 3/20/2020 5:00:00 PM vs 3/20/2020).
 
Upvote 0
after trying .... vlookups
If I have understood correctly, that should work for you. Is this what you mean?
Note that my dates are in d/m/y format.

20 03 23.xlsm
AB
1All_Dates_BusinessClose
220/03/202019,174
319/03/202020,087
418/03/202019,899
517/03/202021,237
616/03/202020,189
713/03/202023,186
812/03/202021,201
911/03/202023,553
1010/03/202025,018
119/03/202023,851
12
13
14All_Dates_Calendar
1520/03/202019,174
1619/03/202020,087
1718/03/202019,899
1817/03/202021,237
1916/03/202020,189
2015/03/2020 
2114/03/2020 
2213/03/202023,186
2312/03/202021,201
2411/03/202023,553
2510/03/202025,018
269/03/202023,851
278/03/2020 
287/03/2020 
Lookup
Cell Formulas
RangeFormula
B15:B28B15=IFERROR(VLOOKUP(A15,A$2:B$11,2,0),"")
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top