Is this possible - Change dates in URL?

Excel Help for Is This Possible - Change Dates In Url? in Automating Data Analysis Tasks

Is This Possible - Change Dates In Url?

Rate this: (3/5 from 1 vote)

	Hi all, thanks so much in advance! I'm researching hotel rates over the course of several days and am trying to sort out a way to essentially update a collection of links on specific hotels (via Orbitz, Kayak, etc) to reflect daily date changes. For example, here's a URL for a specific hotel on Kayak for a 1-night stay today: "http://www.kayak.com/hotels#BEST-WESTERN-PLUS-Grosvenor-Airport-Hotel,San-Francisco,CA-c13852-h33965/2011-10-17/2011-10-18/2guests" So tomorrow I want to come to this sheet, either update a master date field or otherwise increment those date values by 1. I could probably get away with some code that applies today's date straight away but the other 1/2 of this task is to have another batch (just in a 2nd Sheet?) of links pointing to tomorrow's date (hence why a single solution that can be applied to both Sheets might me best vs 2 different solutions). This may look like a simple Find/Replace function but I think it might be more complicated because I am also trying to manipulate URLs from 3 other travel sites that all embed the date in their URLs differently (ie, "10/17/2011...10/18/2011", "checkin_monthday=17&checkin_year_month=2011-10&checkout_monthday=18&checkout_year_month=2011-10", & "hotel.chkin=10%2F17%2F11&hotel.chkout=10%2F18%2F11") Add to it some numeric strings in these URLs that might coincidentally match the 2 digit date values and throw off a regular find/replace function.  Is this at all possible?? I'm using Excel 2003 but will upgrade if necessary! Thanks again!
	Posted by DMKPDX on 18 Oct 2011

Replies - Displaying 1 to 6 of 6

Order Replies By: Most recent | Chronological | Highest Rated

Hmmm Rate this: (3/5 from 1 vote) I think I see what you're getting at. So new URLs would be created from the date and city cells? If that's actually easier function-wise then I'm all for it. I'll always favor the elegant solution but I'm not against a less-sexy method so long as it gets the job done in this case. I want to say thanks again Excel Helper. I'm excited to see what you can come up with here.

Posted by DMKPDX on 19 Oct 2011

	Rethinking the logic Rate this: (3/5 from 1 vote) To do this, we need to create a user defined functions with parameters that match the logic. Before this I wondered whether there is not a more efficient and simply approach.  If there were two cells with dates in them (and a list of destination cities), the combinations of URLs could be created by using DAY, MONTH and YEAR functions on the date cells and references to the city cells.  This way, simply updating the date cells will update the URLs.  Is this a viable solution?
	Excel Business Forums Administrator
	Posted by Excel Helper on 18 Oct 2011

	Ok Rate this: (3/5 from 1 vote) I'm nowhere near as proficient with Excel as you clearly are - what would it take for you to conjure up such a custom function for this issue?
	Posted by DMKPDX on 18 Oct 2011

	Custom Function required Rate this: (3/5 from 1 vote) Since each URL format and date construction is very different, you will need a custom user defined function.  You can use the MID and FIND functions, but will need to test the case for each URL type such as taking the Left 13 characters to evaluate which set of instructions to use.
	Excel Business Forums Administrator
	Posted by Excel Helper on 18 Oct 2011

There's hope, hooray! Rate this: (3/5 from 1 vote) First off Excel Helper, Thank You for such a quick response! I'm quite impressed.  I think out of your 3 suggestions, option 3 might be the most applicable. Partly because I'm hoping such code could manipulate the dates among the various URL structures I'm dealing with. Here are the 5 URL types I'm working with - hopefully you know of a way to execute one function or macro that will run on a spreadsheet with hundreds of such URLs. [In these examples I'd want to take the dates of the 13th and 14th and increment them by a day. For my 2nd need of pulling info for "tomorrow" then perhaps this is a function I can just apply twice to a second Sheet of these links.] http://www.kayak.com/hotels#BEST-WESTERN-PLUS-Grosvenor-Airport-Hotel,San-Francisco,CA-c13852-h33965/2011-10-13/2011-10-14/2guests http://www.expedia.com/San-Francisco-Hotels-Best-Western-Plus-Grosvenor-Airport-Hotel.h7635.Hotel-Information?chkin=10/13/2011&hashTag=roomsAndRates&chkout=10/14/2011&mdpcid=META.kayak.Meta.Hotel.multibook_unpriced.september2009test&rm1=a2&  http://www.booking.com/hotel/us/best-western-grosvenor.html?tab=1&error_url=%2Fhotel%2Fus%2Fbest-western-grosvenor.en-us.html%3Flabel%3Dgog235jc%3Bsid%3D60f01ceba1cbacb0afaf277680128c9f%3B&do_availability_check=on&label=gog235jc&sid=60f01ceba1cbacb0afaf277680128c9f&checkin_monthday=13&checkin_year_month=2011-10&checkout_monthday=14&checkout_year_month=2011-10 http://www.orbitz.com/shop/hotelsearch?type=hotel&hotel.type=keyword&hotel.keyword.key=San+Francisco%2C+CA%2C+United+States&hotel.locId=loc.id%3A4468&hotel.chkin=10%2F13%2F11&hotel.chkout=10%2F14%2F11&hotel.rooms%5B0%5D.adlts=2&hotel.rooms%5B0%5D.chlds=0&hotel.rooms%5B0%5D.chldAge%5B0%5D=&hotel.rooms%5B0%5D.chldAge%5B1%5D=&hotel.rooms%5B0%5D.chldAge%5B2%5D=&hotel.rooms%5B0%5D.chldAge%5B3%5D=&hotel.rooms%5B0%5D.chldAge%5B4%5D=&hotel.rating=&hotel.chain=&hotel.hname=Grosvenor&hotel.couponCode=&search=Search&hsv.showDetails=true&hotel.hid=22815&hotel.hkey=22815_null_null_null_A2:0 http://www.secure-res.com/res/vn4/avail.aspx?hotelid=708&checkindate=10/13/2011&nights=1&rooms=1&adults=2&children=0&childrenages=&trr=7FFFFFD90FDA62&culture=1&culturename=en-US&wtpid=dcs2nqpch00000cl0g9wzdd9o_9j3j           [This 5th URL doesn't show the "check-out" date, just the check-in date and 'nights=1'.]  Thank you so much.

Posted by DMKPDX on 18 Oct 2011

	Getting the Dates from the URL Strings Rate this: (3/5 from 1 vote) Once you have returned the dates from the URLs then you can add to the date values and then append back the rest of the URL using the '&' symbol or CONCATENATE function. The challenge, then, is to extract the date strings whereby there are several possibilities: If the URL is always the same length before the date string then a simple MID function will do it. In your first 'Kayak' URL the function =MID(A1,102,10) gets the first date assuming the URL is in cell A1. If the length changes but not by much then the start character position (in the example at 102) can be found such as using the find function =FIND("/",A1,80) which returns 101 so 1 has to added within the MID function. The Text to Columns menu item to split each URL by the '/' or '=' character thereby extracting each part including dates. Some VBA code could be used to analyze the URL structure and perform the required changes.
	Excel Business Forums Administrator
	Posted by Excel Helper on 18 Oct 2011

Displaying page 1 of 1