How to edit a Hyperlink path in Excel?

Posted on 2015-02-23
Last Modified: 2016-02-11
Hi, I have this Hyperlink path "\\\London\FinanceShared\Finance\EMEA Mortgages\2015\01-January 2015\PVG\Reporting\1 - PV Month End Reporting Pack.xlsx" in Excel . The Folder "01-January 2015" changes every month so how can I edit it so it automatically changes Month on an Excel Spreadsheet ? I can do it with a Formula eg. Format(Now,"DD MM YYYY") but not a hyperlink
Question by:Justincut
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
LVL 24

Expert Comment

by:Phillip Burton
ID: 40625516
You can use a formula hyperlink instead, and you can generate the path using a formula.
=Hyperlink(PathStart & Format(Now,"DD MM YYYY") & PathEnd,"Open")
LVL 85

Expert Comment

by:Rory Archibald
ID: 40625522

You'll want the TEXT function rather than Format, which is VBA, though.

TEXT(TODAY(),"dd mm yyyy")
LVL 33

Expert Comment

by:Rob Henson
ID: 40625531
Looks like you will also need to formulate the Year folder part as well:


Also rather than using TODAY() it might be worth having a fixed date somewhere in your sheet and refer to that instead.

Otherwise, as soon as the new month occurs the calculations will change and you may not have saved to a new file by that time.

Rob H
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 40625544
Hi Rob, if I use a Fixed date in Cell A1 of "01-Jan-15" and my path is this: \\\London\FinanceShared\Finance\EMEA Mortgages\2015\01-January 2015\PVG\Reporting\1 - PV Month End Reporting Pack.xlsx

What would be the full formula for the Hyperlink?
LVL 85

Expert Comment

by:Rory Archibald
ID: 40625557
As Phillip indicated earlier, it would be:

=HYPERLINK("\\\London\FinanceShared\Finance\EMEA Mortgages\"&TEXT(A1,"yyyy\\mm-mmmm-yy")&"\PVG\Reporting\1 - PV Month End Reporting Pack.xlsx","text to display")

I've assumed that the 01 in 01 January is a month number, not a day.
LVL 24

Expert Comment

by:Phillip Burton
ID: 40625568
If it's a date, then you yyyy\\dd-mmmm-yy

Author Comment

ID: 40625577
It works. Cheers! However, is there a way I can see the full path so colleagues can see it changing from Jan to Feb? Just like when you insert a Hyperlink within Excel but with a visible variable.
LVL 24

Accepted Solution

Phillip Burton earned 500 total points
ID: 40625579
Delete the ,"Open"

in other words, instead of:




Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question