Excel fill dates except Sunday
I want to fill a series of dates but Skip Sundays. When I right click the fill handle it only has weekday option, which will skip Saturday too. That's not what I need. How do I approach this? thanks!
microsoft-excel
asked Apr 2 '15 at 21:20
MeidiMeidi
11824
add a comment |
I want to fill a series of dates but Skip Sundays. When I right click the fill handle it only has weekday option, which will skip Saturday too. That's not what I need. How do I approach this? thanks!
microsoft-excel
asked Apr 2 '15 at 21:20
MeidiMeidi
11824
add a comment |
I want to fill a series of dates but Skip Sundays. When I right click the fill handle it only has weekday option, which will skip Saturday too. That's not what I need. How do I approach this? thanks!
microsoft-excel
asked Apr 2 '15 at 21:20
MeidiMeidi
11824
I want to fill a series of dates but Skip Sundays. When I right click the fill handle it only has weekday option, which will skip Saturday too. That's not what I need. How do I approach this? thanks!
microsoft-excel
microsoft-excel
asked Apr 2 '15 at 21:20
MeidiMeidi
11824
asked Apr 2 '15 at 21:20
MeidiMeidi
11824
asked Apr 2 '15 at 21:20
MeidiMeidi
11824
asked Apr 2 '15 at 21:20
MeidiMeidi
11824
asked Apr 2 '15 at 21:20
MeidiMeidi
11824
11824
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Here's a workaround:
Method 1:
You could fill all days and in an adjacent column add a WEEKDAY function pointing at the dates column. Next, AutoFilter the weekday column to show Sundays only and delete all results.
Method 2:
Instead of the simple WEEKDAY function, you could define: =IF(WEEKDAY(A2,1)=1,"Sunday",A2)
Fill it to all cells in a neighboring column. After that select all rows, and use the "remove duplicates" function in the "data" tab. Select the column you just filled and when all duplicates (Sundays) are deleted, only one line (the first occurrence of "Sunday") will remain which you'll have to delete manually.
answered Apr 3 '15 at 0:06
nheenhee
11310
Thank you for your help!!! I just can't remove duplicates or delete extra rows since there are data I need to keep. But I really appreciate your help
– Meidi
Apr 3 '15 at 17:37
add a comment |
I found this answer more suitable for my situation.
=IF(WEEKDAY(INDIRECT("A" & ROW() - 1))=7,INDIRECT("A" & ROW() -1) + 2, INDIRECT("A" & ROW() -1 ) +1 )
answered Apr 3 '15 at 17:36
MeidiMeidi
11824
add a comment |
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
});
}
});
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%2f897251%2fexcel-fill-dates-except-sunday%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Here's a workaround:
Method 1:
You could fill all days and in an adjacent column add a WEEKDAY function pointing at the dates column. Next, AutoFilter the weekday column to show Sundays only and delete all results.
Method 2:
Instead of the simple WEEKDAY function, you could define: =IF(WEEKDAY(A2,1)=1,"Sunday",A2)
Fill it to all cells in a neighboring column. After that select all rows, and use the "remove duplicates" function in the "data" tab. Select the column you just filled and when all duplicates (Sundays) are deleted, only one line (the first occurrence of "Sunday") will remain which you'll have to delete manually.
answered Apr 3 '15 at 0:06
nheenhee
11310
Thank you for your help!!! I just can't remove duplicates or delete extra rows since there are data I need to keep. But I really appreciate your help
– Meidi
Apr 3 '15 at 17:37
add a comment |
Here's a workaround:
Method 1:
You could fill all days and in an adjacent column add a WEEKDAY function pointing at the dates column. Next, AutoFilter the weekday column to show Sundays only and delete all results.
Method 2:
Instead of the simple WEEKDAY function, you could define: =IF(WEEKDAY(A2,1)=1,"Sunday",A2)
Fill it to all cells in a neighboring column. After that select all rows, and use the "remove duplicates" function in the "data" tab. Select the column you just filled and when all duplicates (Sundays) are deleted, only one line (the first occurrence of "Sunday") will remain which you'll have to delete manually.
answered Apr 3 '15 at 0:06
nheenhee
11310
Thank you for your help!!! I just can't remove duplicates or delete extra rows since there are data I need to keep. But I really appreciate your help
– Meidi
Apr 3 '15 at 17:37
add a comment |
Here's a workaround:
Method 1:
You could fill all days and in an adjacent column add a WEEKDAY function pointing at the dates column. Next, AutoFilter the weekday column to show Sundays only and delete all results.
Method 2:
Instead of the simple WEEKDAY function, you could define: =IF(WEEKDAY(A2,1)=1,"Sunday",A2)
Fill it to all cells in a neighboring column. After that select all rows, and use the "remove duplicates" function in the "data" tab. Select the column you just filled and when all duplicates (Sundays) are deleted, only one line (the first occurrence of "Sunday") will remain which you'll have to delete manually.
answered Apr 3 '15 at 0:06
nheenhee
11310
Here's a workaround:
Method 1:
You could fill all days and in an adjacent column add a WEEKDAY function pointing at the dates column. Next, AutoFilter the weekday column to show Sundays only and delete all results.
Method 2:
Instead of the simple WEEKDAY function, you could define: =IF(WEEKDAY(A2,1)=1,"Sunday",A2)
Fill it to all cells in a neighboring column. After that select all rows, and use the "remove duplicates" function in the "data" tab. Select the column you just filled and when all duplicates (Sundays) are deleted, only one line (the first occurrence of "Sunday") will remain which you'll have to delete manually.
answered Apr 3 '15 at 0:06
nheenhee
11310
edited Apr 3 '15 at 3:38
answered Apr 3 '15 at 0:06
nheenhee
11310
answered Apr 3 '15 at 0:06
nheenhee
11310
answered Apr 3 '15 at 0:06
nheenhee
11310
11310
Thank you for your help!!! I just can't remove duplicates or delete extra rows since there are data I need to keep. But I really appreciate your help
– Meidi
Apr 3 '15 at 17:37
add a comment |
Thank you for your help!!! I just can't remove duplicates or delete extra rows since there are data I need to keep. But I really appreciate your help
– Meidi
Apr 3 '15 at 17:37
Thank you for your help!!! I just can't remove duplicates or delete extra rows since there are data I need to keep. But I really appreciate your help
– Meidi
Apr 3 '15 at 17:37
Thank you for your help!!! I just can't remove duplicates or delete extra rows since there are data I need to keep. But I really appreciate your help
– Meidi
Apr 3 '15 at 17:37
add a comment |
I found this answer more suitable for my situation.
=IF(WEEKDAY(INDIRECT("A" & ROW() - 1))=7,INDIRECT("A" & ROW() -1) + 2, INDIRECT("A" & ROW() -1 ) +1 )
answered Apr 3 '15 at 17:36
MeidiMeidi
11824
add a comment |
I found this answer more suitable for my situation.
=IF(WEEKDAY(INDIRECT("A" & ROW() - 1))=7,INDIRECT("A" & ROW() -1) + 2, INDIRECT("A" & ROW() -1 ) +1 )
answered Apr 3 '15 at 17:36
MeidiMeidi
11824
add a comment |
I found this answer more suitable for my situation.
=IF(WEEKDAY(INDIRECT("A" & ROW() - 1))=7,INDIRECT("A" & ROW() -1) + 2, INDIRECT("A" & ROW() -1 ) +1 )
answered Apr 3 '15 at 17:36
MeidiMeidi
11824
I found this answer more suitable for my situation.
=IF(WEEKDAY(INDIRECT("A" & ROW() - 1))=7,INDIRECT("A" & ROW() -1) + 2, INDIRECT("A" & ROW() -1 ) +1 )
answered Apr 3 '15 at 17:36
MeidiMeidi
11824
answered Apr 3 '15 at 17:36
MeidiMeidi
11824
answered Apr 3 '15 at 17:36
MeidiMeidi
11824
answered Apr 3 '15 at 17:36
MeidiMeidi
11824
11824
add a comment |
add a comment |
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.
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%2f897251%2fexcel-fill-dates-except-sunday%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
