updating a part/the date of the URL in excel
Basically, I haven't done much of excel formulation so this is new to me. I have an excel file which has a few hundreds of URL in it. now, most of these URL's are a search from different websites which include the dates in them. for example:
https://www.ft.com/search?q=investment&dateTo=2018-11-21&dateFrom=2018-11-21&sort=date
so the q=investment is what I'm searching which is static but the part To=2018-11-21&dateFrom=2018-11-21 depends on the date of everytime I open the file. I wonder if there is a way to formulate this URL so everytime I open the file, the dateTo=2018-11-21, and dateFrom=2018-11-21 gets updated by itself to todays date?
excel excel-formula
asked Nov 21 '18 at 9:30
AliAli
95421231
add a comment |
Basically, I haven't done much of excel formulation so this is new to me. I have an excel file which has a few hundreds of URL in it. now, most of these URL's are a search from different websites which include the dates in them. for example:
https://www.ft.com/search?q=investment&dateTo=2018-11-21&dateFrom=2018-11-21&sort=date
so the q=investment is what I'm searching which is static but the part To=2018-11-21&dateFrom=2018-11-21 depends on the date of everytime I open the file. I wonder if there is a way to formulate this URL so everytime I open the file, the dateTo=2018-11-21, and dateFrom=2018-11-21 gets updated by itself to todays date?
excel excel-formula
asked Nov 21 '18 at 9:30
AliAli
95421231
add a comment |
Basically, I haven't done much of excel formulation so this is new to me. I have an excel file which has a few hundreds of URL in it. now, most of these URL's are a search from different websites which include the dates in them. for example:
https://www.ft.com/search?q=investment&dateTo=2018-11-21&dateFrom=2018-11-21&sort=date
so the q=investment is what I'm searching which is static but the part To=2018-11-21&dateFrom=2018-11-21 depends on the date of everytime I open the file. I wonder if there is a way to formulate this URL so everytime I open the file, the dateTo=2018-11-21, and dateFrom=2018-11-21 gets updated by itself to todays date?
excel excel-formula
asked Nov 21 '18 at 9:30
AliAli
95421231
Basically, I haven't done much of excel formulation so this is new to me. I have an excel file which has a few hundreds of URL in it. now, most of these URL's are a search from different websites which include the dates in them. for example:
https://www.ft.com/search?q=investment&dateTo=2018-11-21&dateFrom=2018-11-21&sort=date
so the q=investment is what I'm searching which is static but the part To=2018-11-21&dateFrom=2018-11-21 depends on the date of everytime I open the file. I wonder if there is a way to formulate this URL so everytime I open the file, the dateTo=2018-11-21, and dateFrom=2018-11-21 gets updated by itself to todays date?
excel excel-formula
excel excel-formula
asked Nov 21 '18 at 9:30
AliAli
95421231
asked Nov 21 '18 at 9:30
AliAli
95421231
asked Nov 21 '18 at 9:30
AliAli
95421231
asked Nov 21 '18 at 9:30
AliAli
95421231
asked Nov 21 '18 at 9:30
AliAli
95421231
95421231
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Let's assume the original URLs are in column B, and you put today's date =TODAY() in cell A1, you then put the following formula in cell C1 :=SUBSTITUTE(B1;"2018-11-21";TEXT($A$1;"yyyy-mm-dd"))
And copy down as far as needed.
I hard coded the "2018-11-21" old date in the formula, but it would of course be easier to re-use if you put the old date in eg. A2 as a text string (very important, not as a date!)
If you want to have hyperlinks as output, just wrap with HYPERLINK() function:=HYPERLINK(SUBSTITUTE(B1;"2018-11-21";TEXT($A$1;"yyyy-mm-dd")))
answered Nov 21 '18 at 9:56
Peter K.Peter K.
763212
A1: =TODAY() B1: my url C1: =SUBSTITUTE(B1;"2018-11-21";TEXT($A$1;"yyyy-mm-dd")) it doesn't work!
– Ali
Nov 21 '18 at 10:10
1
Did you change the formula delimiter? In some countries you have to change the ; (semicolon) with , (comma).
– Peter K.
Nov 21 '18 at 10:14
1
And please note that with the example in your question, running this today in Excel no changes will be made, as it is today 2018-11-21 ...
– Peter K.
Nov 21 '18 at 10:18
the comma did the trick, thanks a lot but since its a formula, the result is not a URL. I need to find a way to make it to a URL. Is there a trick for that as well or i have to copy past them ?
– Ali
Nov 21 '18 at 10:19
1
@Ali Very easy, just wrap inHYPERLINK()function. I updated my answer with this.
– Peter K.
Nov 21 '18 at 13:44
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53408929%2fupdating-a-part-the-date-of-the-url-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Let's assume the original URLs are in column B, and you put today's date =TODAY() in cell A1, you then put the following formula in cell C1 :=SUBSTITUTE(B1;"2018-11-21";TEXT($A$1;"yyyy-mm-dd"))
And copy down as far as needed.
I hard coded the "2018-11-21" old date in the formula, but it would of course be easier to re-use if you put the old date in eg. A2 as a text string (very important, not as a date!)
If you want to have hyperlinks as output, just wrap with HYPERLINK() function:=HYPERLINK(SUBSTITUTE(B1;"2018-11-21";TEXT($A$1;"yyyy-mm-dd")))
answered Nov 21 '18 at 9:56
Peter K.Peter K.
763212
A1: =TODAY() B1: my url C1: =SUBSTITUTE(B1;"2018-11-21";TEXT($A$1;"yyyy-mm-dd")) it doesn't work!
– Ali
Nov 21 '18 at 10:10
1
Did you change the formula delimiter? In some countries you have to change the ; (semicolon) with , (comma).
– Peter K.
Nov 21 '18 at 10:14
1
And please note that with the example in your question, running this today in Excel no changes will be made, as it is today 2018-11-21 ...
– Peter K.
Nov 21 '18 at 10:18
the comma did the trick, thanks a lot but since its a formula, the result is not a URL. I need to find a way to make it to a URL. Is there a trick for that as well or i have to copy past them ?
– Ali
Nov 21 '18 at 10:19
1
@Ali Very easy, just wrap inHYPERLINK()function. I updated my answer with this.
– Peter K.
Nov 21 '18 at 13:44
add a comment |
Let's assume the original URLs are in column B, and you put today's date =TODAY() in cell A1, you then put the following formula in cell C1 :=SUBSTITUTE(B1;"2018-11-21";TEXT($A$1;"yyyy-mm-dd"))
And copy down as far as needed.
I hard coded the "2018-11-21" old date in the formula, but it would of course be easier to re-use if you put the old date in eg. A2 as a text string (very important, not as a date!)
If you want to have hyperlinks as output, just wrap with HYPERLINK() function:=HYPERLINK(SUBSTITUTE(B1;"2018-11-21";TEXT($A$1;"yyyy-mm-dd")))
answered Nov 21 '18 at 9:56
Peter K.Peter K.
763212
A1: =TODAY() B1: my url C1: =SUBSTITUTE(B1;"2018-11-21";TEXT($A$1;"yyyy-mm-dd")) it doesn't work!
– Ali
Nov 21 '18 at 10:10
1
Did you change the formula delimiter? In some countries you have to change the ; (semicolon) with , (comma).
– Peter K.
Nov 21 '18 at 10:14
1
And please note that with the example in your question, running this today in Excel no changes will be made, as it is today 2018-11-21 ...
– Peter K.
Nov 21 '18 at 10:18
the comma did the trick, thanks a lot but since its a formula, the result is not a URL. I need to find a way to make it to a URL. Is there a trick for that as well or i have to copy past them ?
– Ali
Nov 21 '18 at 10:19
1
@Ali Very easy, just wrap inHYPERLINK()function. I updated my answer with this.
– Peter K.
Nov 21 '18 at 13:44
add a comment |
Let's assume the original URLs are in column B, and you put today's date =TODAY() in cell A1, you then put the following formula in cell C1 :=SUBSTITUTE(B1;"2018-11-21";TEXT($A$1;"yyyy-mm-dd"))
And copy down as far as needed.
I hard coded the "2018-11-21" old date in the formula, but it would of course be easier to re-use if you put the old date in eg. A2 as a text string (very important, not as a date!)
If you want to have hyperlinks as output, just wrap with HYPERLINK() function:=HYPERLINK(SUBSTITUTE(B1;"2018-11-21";TEXT($A$1;"yyyy-mm-dd")))
answered Nov 21 '18 at 9:56
Peter K.Peter K.
763212
Let's assume the original URLs are in column B, and you put today's date =TODAY() in cell A1, you then put the following formula in cell C1 :=SUBSTITUTE(B1;"2018-11-21";TEXT($A$1;"yyyy-mm-dd"))
And copy down as far as needed.
I hard coded the "2018-11-21" old date in the formula, but it would of course be easier to re-use if you put the old date in eg. A2 as a text string (very important, not as a date!)
If you want to have hyperlinks as output, just wrap with HYPERLINK() function:=HYPERLINK(SUBSTITUTE(B1;"2018-11-21";TEXT($A$1;"yyyy-mm-dd")))
answered Nov 21 '18 at 9:56
Peter K.Peter K.
763212
edited Nov 21 '18 at 13:45
answered Nov 21 '18 at 9:56
Peter K.Peter K.
763212
answered Nov 21 '18 at 9:56
Peter K.Peter K.
763212
answered Nov 21 '18 at 9:56
Peter K.Peter K.
763212
763212
A1: =TODAY() B1: my url C1: =SUBSTITUTE(B1;"2018-11-21";TEXT($A$1;"yyyy-mm-dd")) it doesn't work!
– Ali
Nov 21 '18 at 10:10
1
Did you change the formula delimiter? In some countries you have to change the ; (semicolon) with , (comma).
– Peter K.
Nov 21 '18 at 10:14
1
And please note that with the example in your question, running this today in Excel no changes will be made, as it is today 2018-11-21 ...
– Peter K.
Nov 21 '18 at 10:18
the comma did the trick, thanks a lot but since its a formula, the result is not a URL. I need to find a way to make it to a URL. Is there a trick for that as well or i have to copy past them ?
– Ali
Nov 21 '18 at 10:19
1
@Ali Very easy, just wrap inHYPERLINK()function. I updated my answer with this.
– Peter K.
Nov 21 '18 at 13:44
add a comment |
A1: =TODAY() B1: my url C1: =SUBSTITUTE(B1;"2018-11-21";TEXT($A$1;"yyyy-mm-dd")) it doesn't work!
– Ali
Nov 21 '18 at 10:10
1
Did you change the formula delimiter? In some countries you have to change the ; (semicolon) with , (comma).
– Peter K.
Nov 21 '18 at 10:14
1
And please note that with the example in your question, running this today in Excel no changes will be made, as it is today 2018-11-21 ...
– Peter K.
Nov 21 '18 at 10:18
the comma did the trick, thanks a lot but since its a formula, the result is not a URL. I need to find a way to make it to a URL. Is there a trick for that as well or i have to copy past them ?
– Ali
Nov 21 '18 at 10:19
1
@Ali Very easy, just wrap inHYPERLINK()function. I updated my answer with this.
– Peter K.
Nov 21 '18 at 13:44
A1: =TODAY() B1: my url C1: =SUBSTITUTE(B1;"2018-11-21";TEXT($A$1;"yyyy-mm-dd")) it doesn't work!
– Ali
Nov 21 '18 at 10:10
A1: =TODAY() B1: my url C1: =SUBSTITUTE(B1;"2018-11-21";TEXT($A$1;"yyyy-mm-dd")) it doesn't work!
– Ali
Nov 21 '18 at 10:10
1
1
Did you change the formula delimiter? In some countries you have to change the ; (semicolon) with , (comma).
– Peter K.
Nov 21 '18 at 10:14
Did you change the formula delimiter? In some countries you have to change the ; (semicolon) with , (comma).
– Peter K.
Nov 21 '18 at 10:14
1
1
And please note that with the example in your question, running this today in Excel no changes will be made, as it is today 2018-11-21 ...
– Peter K.
Nov 21 '18 at 10:18
And please note that with the example in your question, running this today in Excel no changes will be made, as it is today 2018-11-21 ...
– Peter K.
Nov 21 '18 at 10:18
the comma did the trick, thanks a lot but since its a formula, the result is not a URL. I need to find a way to make it to a URL. Is there a trick for that as well or i have to copy past them ?
– Ali
Nov 21 '18 at 10:19
the comma did the trick, thanks a lot but since its a formula, the result is not a URL. I need to find a way to make it to a URL. Is there a trick for that as well or i have to copy past them ?
– Ali
Nov 21 '18 at 10:19
1
1
@Ali Very easy, just wrap in
HYPERLINK() function. I updated my answer with this.– Peter K.
Nov 21 '18 at 13:44
@Ali Very easy, just wrap in
HYPERLINK() function. I updated my answer with this.– Peter K.
Nov 21 '18 at 13:44
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53408929%2fupdating-a-part-the-date-of-the-url-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
