Changing a hyperlink based on the date

Question

Hello,

I am using Excel 2010.

All I want to do is have part of a hyperlink change to reflect yesterday's date. For example, I want the hyperlink today to have "6/16/2014" rather than today's date of "6/17/2014". Suppose the base link is:

http://www.cmegroup.com/trading/energy/crude-oil/wts-argus-vs-wti-calendar-spread-swap-futures_quotes_settlements_futures.html?cmeTradeDate=03%2F04%2F2014#tradeDate=

The last part that has "#tradeDate=" is usually followed by a date to indicate day the data originates. EX: http://www.cmegroup.com/trading/energy/crude-oil/wts-argus-vs-wti-calendar-spread-swap-futures_quotes_settlements_futures.html?cmeTradeDate=03%2F04%2F2014#tradeDate=06/16/2014

My question is can you use a formula or macro to change part of a hyperlink in a cell? I can easily get yesterday's date, but I want to implement that into the hyperlink.

Thanks!

Answer 1

If you entry is in A1, use following formula to show yesterday's date-

 =HYPERLINK(A1&TEXT(TODAY()-1,"mm/dd/yyyy"))

In general, you can use =HYPERLINK(A1&"Your String") if your string is at the end.

Answer 2

Perfect. Thank you very much. Your solution does exactly what I wanted it to do, but the link doesn't work the way I thought it would. Thank you though!