Activate hyperlink base on date

  • Good day all!!!


    I have a file with data on certain cells that I wanted to hyperlink to another sheets. I wanted the hyperlink to be activated ONLY after a certain date - system date (say, if system date is greater than Jan 5, 2005, make a hyperlink on cell b1 in sheet1 to cell a2 in sheet2; if system date is greater than Jan12, make a hyperlink on cell b3 in sheet1 to cell a2 in sheet3, and so on). The link will remain active, of course, unless the system date is changed.


    Can someone help me on this pls...


    A million thanks...

  • Re: Activate hyperlink base on date


    Hi lorenzod,


    You can place a IF() condition around the Hyperlink formula.
    For this example you can enter a date in cell D1 to determine which link is displayed.


    Code
    B1: =IF(D1>TODAY(),HYPERLINK("#Sheet2!A2","Sheet2!A2"),"")
    B2: =IF(D1<TODAY(),HYPERLINK("#Sheet3!A2","Sheet3!A2"),"")


    Note the use of the hash (#) is deliberate in order to make the Hyperlink formula work.

    [h4]Cheers
    Andy
    [/h4]

  • Re: Activate hyperlink base on date


    Hi Andy,



    Thanks for the reply...
    And sorry for the delayed response....


    I tried ur formula... and it worked...
    But I wanted also to display the text, but not activate the hyperlink...


    I put it this way:


    =IF(D1>TODAY(),HYPERLINK("#Lesson1!A2","Lesson1"),"Lesson 1")


    Here's my problem now:


    If the date in D1, is less than TODAY(), there is still a hyperlink in the text Lesson 1, and once u click it... a message will display, "Cannot open the specified file."


    Is there something missing in my formula?


    Thanks again....

  • Re: Activate hyperlink base on date


    Credit to fellow MVP Harlad Staff for suggesting the following...


    Use a reference to the same cell. It will stop the error message.

    Code
    D2: =IF(D1>TODAY(),HYPERLINK("#Lesson1!A2","Lesson1"),HYPERLINK("#d2","Lesson 1"))

    [h4]Cheers
    Andy
    [/h4]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!