Excel Formula, get the previous wednesday date
3
I have the following formula,
=WORKDAY(TODAY(),-2)
Which gets me Jan 30th.
Is there anyway I can get Jan 30th without putting the -2 in this formula? I want he formula to always get the previous Wednesdays date, but I manually just subtract the todays workday to get the previous Wednesday.
microsoft-excel
asked yesterday
excelguyexcelguy
758
add a comment |
3
I have the following formula,
=WORKDAY(TODAY(),-2)
Which gets me Jan 30th.
Is there anyway I can get Jan 30th without putting the -2 in this formula? I want he formula to always get the previous Wednesdays date, but I manually just subtract the todays workday to get the previous Wednesday.
microsoft-excel
asked yesterday
excelguyexcelguy
758
add a comment |
3
3
3
I have the following formula,
=WORKDAY(TODAY(),-2)
Which gets me Jan 30th.
Is there anyway I can get Jan 30th without putting the -2 in this formula? I want he formula to always get the previous Wednesdays date, but I manually just subtract the todays workday to get the previous Wednesday.
microsoft-excel
asked yesterday
excelguyexcelguy
758
I have the following formula,
=WORKDAY(TODAY(),-2)
Which gets me Jan 30th.
Is there anyway I can get Jan 30th without putting the -2 in this formula? I want he formula to always get the previous Wednesdays date, but I manually just subtract the todays workday to get the previous Wednesday.
microsoft-excel
microsoft-excel
asked yesterday
excelguyexcelguy
758
asked yesterday
excelguyexcelguy
758
asked yesterday
excelguyexcelguy
758
asked yesterday
excelguyexcelguy
758
asked yesterday
excelguyexcelguy
758
758
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
8
How about:
=TODAY() - MOD(TODAY(), 7) + 4
Which means get first day of current week (Saturday), then add 4 = Wednesday, (optionally -7 for the previous week).
If you want the previous week (if you are still in Sunday or Monday), then use if for MOD(TODAY(), 7)
answered yesterday
Ahmed AshourAhmed Ashour
1,3201714
thank you ahmed!
– excelguy
yesterday
1
I'm having trouble following the explanation. Why is the first day of the current week a Saturday (as opposed to Monday or Sunday depending on what calendar system you use)? If you add 4 to a Saturday I can see that you get Wednesday, but why would -7 get you to the previous Wednesday? It seems to me that Saturday - 7 = Saturday. Note: I'm not questioning the formula, but merely the explanation that goes with it.
– Jon Bentley
yesterday
@JonBentley the -7 is meant to be appended to the shown formula, not subsituted in for the +4. As to why MOD(TODAY(),7) produces this very helpful result, I'm also curious.
– Alex M
yesterday
1
@AlexM Dates in excel are like an integer. Today, for example, is 43497. If you divide it by 7 using the MOD formula, will always return 6 if a day is Friday. This works because the very first day excel accepts. By dividing by 7, you guarantee that you will always get a number from 0-6 (Because 7 / 7 = 1, which returns nothing). 8/7 returns 1 for everyone and 1 not divided.
– Moacir
yesterday
So now you went to the Saturday of this week(considering Saturday would be the first day), then the +4 corrects it to Wednesday
– Moacir
yesterday
|
show 2 more comments
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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
});
}
});
draft saved
draft discarded
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%2fsuperuser.com%2fquestions%2f1401032%2fexcel-formula-get-the-previous-wednesday-date%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
8
How about:
=TODAY() - MOD(TODAY(), 7) + 4
Which means get first day of current week (Saturday), then add 4 = Wednesday, (optionally -7 for the previous week).
If you want the previous week (if you are still in Sunday or Monday), then use if for MOD(TODAY(), 7)
answered yesterday
Ahmed AshourAhmed Ashour
1,3201714
thank you ahmed!
– excelguy
yesterday
1
I'm having trouble following the explanation. Why is the first day of the current week a Saturday (as opposed to Monday or Sunday depending on what calendar system you use)? If you add 4 to a Saturday I can see that you get Wednesday, but why would -7 get you to the previous Wednesday? It seems to me that Saturday - 7 = Saturday. Note: I'm not questioning the formula, but merely the explanation that goes with it.
– Jon Bentley
yesterday
@JonBentley the -7 is meant to be appended to the shown formula, not subsituted in for the +4. As to why MOD(TODAY(),7) produces this very helpful result, I'm also curious.
– Alex M
yesterday
1
@AlexM Dates in excel are like an integer. Today, for example, is 43497. If you divide it by 7 using the MOD formula, will always return 6 if a day is Friday. This works because the very first day excel accepts. By dividing by 7, you guarantee that you will always get a number from 0-6 (Because 7 / 7 = 1, which returns nothing). 8/7 returns 1 for everyone and 1 not divided.
– Moacir
yesterday
So now you went to the Saturday of this week(considering Saturday would be the first day), then the +4 corrects it to Wednesday
– Moacir
yesterday
|
show 2 more comments
8
How about:
=TODAY() - MOD(TODAY(), 7) + 4
Which means get first day of current week (Saturday), then add 4 = Wednesday, (optionally -7 for the previous week).
If you want the previous week (if you are still in Sunday or Monday), then use if for MOD(TODAY(), 7)
answered yesterday
Ahmed AshourAhmed Ashour
1,3201714
thank you ahmed!
– excelguy
yesterday
1
I'm having trouble following the explanation. Why is the first day of the current week a Saturday (as opposed to Monday or Sunday depending on what calendar system you use)? If you add 4 to a Saturday I can see that you get Wednesday, but why would -7 get you to the previous Wednesday? It seems to me that Saturday - 7 = Saturday. Note: I'm not questioning the formula, but merely the explanation that goes with it.
– Jon Bentley
yesterday
@JonBentley the -7 is meant to be appended to the shown formula, not subsituted in for the +4. As to why MOD(TODAY(),7) produces this very helpful result, I'm also curious.
– Alex M
yesterday
1
@AlexM Dates in excel are like an integer. Today, for example, is 43497. If you divide it by 7 using the MOD formula, will always return 6 if a day is Friday. This works because the very first day excel accepts. By dividing by 7, you guarantee that you will always get a number from 0-6 (Because 7 / 7 = 1, which returns nothing). 8/7 returns 1 for everyone and 1 not divided.
– Moacir
yesterday
So now you went to the Saturday of this week(considering Saturday would be the first day), then the +4 corrects it to Wednesday
– Moacir
yesterday
|
show 2 more comments
8
8
8
How about:
=TODAY() - MOD(TODAY(), 7) + 4
Which means get first day of current week (Saturday), then add 4 = Wednesday, (optionally -7 for the previous week).
If you want the previous week (if you are still in Sunday or Monday), then use if for MOD(TODAY(), 7)
answered yesterday
Ahmed AshourAhmed Ashour
1,3201714
How about:
=TODAY() - MOD(TODAY(), 7) + 4
Which means get first day of current week (Saturday), then add 4 = Wednesday, (optionally -7 for the previous week).
If you want the previous week (if you are still in Sunday or Monday), then use if for MOD(TODAY(), 7)
answered yesterday
Ahmed AshourAhmed Ashour
1,3201714
answered yesterday
Ahmed AshourAhmed Ashour
1,3201714
answered yesterday
Ahmed AshourAhmed Ashour
1,3201714
answered yesterday
Ahmed AshourAhmed Ashour
1,3201714
1,3201714
thank you ahmed!
– excelguy
yesterday
1
I'm having trouble following the explanation. Why is the first day of the current week a Saturday (as opposed to Monday or Sunday depending on what calendar system you use)? If you add 4 to a Saturday I can see that you get Wednesday, but why would -7 get you to the previous Wednesday? It seems to me that Saturday - 7 = Saturday. Note: I'm not questioning the formula, but merely the explanation that goes with it.
– Jon Bentley
yesterday
@JonBentley the -7 is meant to be appended to the shown formula, not subsituted in for the +4. As to why MOD(TODAY(),7) produces this very helpful result, I'm also curious.
– Alex M
yesterday
1
@AlexM Dates in excel are like an integer. Today, for example, is 43497. If you divide it by 7 using the MOD formula, will always return 6 if a day is Friday. This works because the very first day excel accepts. By dividing by 7, you guarantee that you will always get a number from 0-6 (Because 7 / 7 = 1, which returns nothing). 8/7 returns 1 for everyone and 1 not divided.
– Moacir
yesterday
So now you went to the Saturday of this week(considering Saturday would be the first day), then the +4 corrects it to Wednesday
– Moacir
yesterday
|
show 2 more comments
thank you ahmed!
– excelguy
yesterday
1
I'm having trouble following the explanation. Why is the first day of the current week a Saturday (as opposed to Monday or Sunday depending on what calendar system you use)? If you add 4 to a Saturday I can see that you get Wednesday, but why would -7 get you to the previous Wednesday? It seems to me that Saturday - 7 = Saturday. Note: I'm not questioning the formula, but merely the explanation that goes with it.
– Jon Bentley
yesterday
@JonBentley the -7 is meant to be appended to the shown formula, not subsituted in for the +4. As to why MOD(TODAY(),7) produces this very helpful result, I'm also curious.
– Alex M
yesterday
1
@AlexM Dates in excel are like an integer. Today, for example, is 43497. If you divide it by 7 using the MOD formula, will always return 6 if a day is Friday. This works because the very first day excel accepts. By dividing by 7, you guarantee that you will always get a number from 0-6 (Because 7 / 7 = 1, which returns nothing). 8/7 returns 1 for everyone and 1 not divided.
– Moacir
yesterday
So now you went to the Saturday of this week(considering Saturday would be the first day), then the +4 corrects it to Wednesday
– Moacir
yesterday
thank you ahmed!
– excelguy
yesterday
thank you ahmed!
– excelguy
yesterday
1
1
I'm having trouble following the explanation. Why is the first day of the current week a Saturday (as opposed to Monday or Sunday depending on what calendar system you use)? If you add 4 to a Saturday I can see that you get Wednesday, but why would -7 get you to the previous Wednesday? It seems to me that Saturday - 7 = Saturday. Note: I'm not questioning the formula, but merely the explanation that goes with it.
– Jon Bentley
yesterday
I'm having trouble following the explanation. Why is the first day of the current week a Saturday (as opposed to Monday or Sunday depending on what calendar system you use)? If you add 4 to a Saturday I can see that you get Wednesday, but why would -7 get you to the previous Wednesday? It seems to me that Saturday - 7 = Saturday. Note: I'm not questioning the formula, but merely the explanation that goes with it.
– Jon Bentley
yesterday
@JonBentley the -7 is meant to be appended to the shown formula, not subsituted in for the +4. As to why MOD(TODAY(),7) produces this very helpful result, I'm also curious.
– Alex M
yesterday
@JonBentley the -7 is meant to be appended to the shown formula, not subsituted in for the +4. As to why MOD(TODAY(),7) produces this very helpful result, I'm also curious.
– Alex M
yesterday
1
1
@AlexM Dates in excel are like an integer. Today, for example, is 43497. If you divide it by 7 using the MOD formula, will always return 6 if a day is Friday. This works because the very first day excel accepts. By dividing by 7, you guarantee that you will always get a number from 0-6 (Because 7 / 7 = 1, which returns nothing). 8/7 returns 1 for everyone and 1 not divided.
– Moacir
yesterday
@AlexM Dates in excel are like an integer. Today, for example, is 43497. If you divide it by 7 using the MOD formula, will always return 6 if a day is Friday. This works because the very first day excel accepts. By dividing by 7, you guarantee that you will always get a number from 0-6 (Because 7 / 7 = 1, which returns nothing). 8/7 returns 1 for everyone and 1 not divided.
– Moacir
yesterday
So now you went to the Saturday of this week(considering Saturday would be the first day), then the +4 corrects it to Wednesday
– Moacir
yesterday
So now you went to the Saturday of this week(considering Saturday would be the first day), then the +4 corrects it to Wednesday
– Moacir
yesterday
|
show 2 more comments
draft saved
draft discarded
Thanks for contributing an answer to Super User!
- 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.
draft saved
draft discarded
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%2fsuperuser.com%2fquestions%2f1401032%2fexcel-formula-get-the-previous-wednesday-date%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
