Unique Excel Date Query/Formula
Is it possible to pre-populate a date in a column after date in adjacent column is entered?
Here is what I have been tasked with:
If date is on or before (<=) the 6th of the month (e.g. 01/01/2019) then the pre-populated date should be 21st of the following month (+1 month) (e.g. 21/02/2019)
If date is after (>) the 6th of the month (e.g. 07/01/2019) then the pre-populated date should be 21st +2 months (e.g. 21/03/2019)
The pre-populated will always be start with the 21st day of the month.
Any help with this is greatly appreciated
Many thanks
RT
microsoft-excel worksheet-function microsoft-excel-2010
asked Jan 23 at 11:56
RockTaylorRockTaylor
1
add a comment |
Is it possible to pre-populate a date in a column after date in adjacent column is entered?
Here is what I have been tasked with:
If date is on or before (<=) the 6th of the month (e.g. 01/01/2019) then the pre-populated date should be 21st of the following month (+1 month) (e.g. 21/02/2019)
If date is after (>) the 6th of the month (e.g. 07/01/2019) then the pre-populated date should be 21st +2 months (e.g. 21/03/2019)
The pre-populated will always be start with the 21st day of the month.
Any help with this is greatly appreciated
Many thanks
RT
microsoft-excel worksheet-function microsoft-excel-2010
asked Jan 23 at 11:56
RockTaylorRockTaylor
1
add a comment |
Is it possible to pre-populate a date in a column after date in adjacent column is entered?
Here is what I have been tasked with:
If date is on or before (<=) the 6th of the month (e.g. 01/01/2019) then the pre-populated date should be 21st of the following month (+1 month) (e.g. 21/02/2019)
If date is after (>) the 6th of the month (e.g. 07/01/2019) then the pre-populated date should be 21st +2 months (e.g. 21/03/2019)
The pre-populated will always be start with the 21st day of the month.
Any help with this is greatly appreciated
Many thanks
RT
microsoft-excel worksheet-function microsoft-excel-2010
asked Jan 23 at 11:56
RockTaylorRockTaylor
1
Is it possible to pre-populate a date in a column after date in adjacent column is entered?
Here is what I have been tasked with:
If date is on or before (<=) the 6th of the month (e.g. 01/01/2019) then the pre-populated date should be 21st of the following month (+1 month) (e.g. 21/02/2019)
If date is after (>) the 6th of the month (e.g. 07/01/2019) then the pre-populated date should be 21st +2 months (e.g. 21/03/2019)
The pre-populated will always be start with the 21st day of the month.
Any help with this is greatly appreciated
Many thanks
RT
microsoft-excel worksheet-function microsoft-excel-2010
microsoft-excel worksheet-function microsoft-excel-2010
asked Jan 23 at 11:56
RockTaylorRockTaylor
1
asked Jan 23 at 11:56
RockTaylorRockTaylor
1
asked Jan 23 at 11:56
RockTaylorRockTaylor
1
asked Jan 23 at 11:56
RockTaylorRockTaylor
1
asked Jan 23 at 11:56
RockTaylorRockTaylor
1
1
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
This is pretty straightforward using the date functions.
Note that I'm in the US, so my dates are MM/DD/YYYY. I've added a December date so you can see how Excel intelligently handles the year if the month goes beyond 12. The formula in B1:
=DATE(YEAR(A1),MONTH(A1)+IF(DAY(A1)>6,2,1),21)
DATE() lets you specify a date by entering the year, month, and day. The target day is always the 21st day of the month. The year is taken as the year of the source date. All of the action happens with the month specification. We start with the month of the source date, then add either 1 or 2 months based on whether the source day is greater than 6. The logic is very human readable and follows what you described in the question.
answered Jan 23 at 12:35
fixer1234fixer1234
18.9k144982
More elegant than mine.
– Mark Fitzgerald
Jan 23 at 12:38
@MarkFitzgerald, great minds think alike. :-) Our formulas are almost the same and it looks like we were both writing answers at the same time.
– fixer1234
Jan 23 at 12:41
add a comment |
If your date was in Cell A1 then this formula in any other cell in row 1 will work:
=IF(DAY(A1)>6,DATE(YEAR(A1),MONTH(A1)+2,21),DATE(YEAR(A1),MONTH(A1)+1,21))
Copy down as required.
answered Jan 23 at 12:37
Mark FitzgeraldMark Fitzgerald
4021311
add a comment |
I prefer the IF function as its clearer to read and understand what is going on, but as an alternative you could use the following:
=DATE(YEAR(A1),MONTH(A1)+1+(DAY(A1)>6),21)
The (DAY(A1)>6) will return either TRUE or FALSE. When this is sent through a math operation, in this case addition, it will be converted to 1 for TRUE and 0 for FALSE.
answered Jan 23 at 13:18
Forward EdForward Ed
844214
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%2f1397433%2funique-excel-date-query-formula%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
This is pretty straightforward using the date functions.
Note that I'm in the US, so my dates are MM/DD/YYYY. I've added a December date so you can see how Excel intelligently handles the year if the month goes beyond 12. The formula in B1:
=DATE(YEAR(A1),MONTH(A1)+IF(DAY(A1)>6,2,1),21)
DATE() lets you specify a date by entering the year, month, and day. The target day is always the 21st day of the month. The year is taken as the year of the source date. All of the action happens with the month specification. We start with the month of the source date, then add either 1 or 2 months based on whether the source day is greater than 6. The logic is very human readable and follows what you described in the question.
answered Jan 23 at 12:35
fixer1234fixer1234
18.9k144982
More elegant than mine.
– Mark Fitzgerald
Jan 23 at 12:38
@MarkFitzgerald, great minds think alike. :-) Our formulas are almost the same and it looks like we were both writing answers at the same time.
– fixer1234
Jan 23 at 12:41
add a comment |
This is pretty straightforward using the date functions.
Note that I'm in the US, so my dates are MM/DD/YYYY. I've added a December date so you can see how Excel intelligently handles the year if the month goes beyond 12. The formula in B1:
=DATE(YEAR(A1),MONTH(A1)+IF(DAY(A1)>6,2,1),21)
DATE() lets you specify a date by entering the year, month, and day. The target day is always the 21st day of the month. The year is taken as the year of the source date. All of the action happens with the month specification. We start with the month of the source date, then add either 1 or 2 months based on whether the source day is greater than 6. The logic is very human readable and follows what you described in the question.
answered Jan 23 at 12:35
fixer1234fixer1234
18.9k144982
More elegant than mine.
– Mark Fitzgerald
Jan 23 at 12:38
@MarkFitzgerald, great minds think alike. :-) Our formulas are almost the same and it looks like we were both writing answers at the same time.
– fixer1234
Jan 23 at 12:41
add a comment |
This is pretty straightforward using the date functions.
Note that I'm in the US, so my dates are MM/DD/YYYY. I've added a December date so you can see how Excel intelligently handles the year if the month goes beyond 12. The formula in B1:
=DATE(YEAR(A1),MONTH(A1)+IF(DAY(A1)>6,2,1),21)
DATE() lets you specify a date by entering the year, month, and day. The target day is always the 21st day of the month. The year is taken as the year of the source date. All of the action happens with the month specification. We start with the month of the source date, then add either 1 or 2 months based on whether the source day is greater than 6. The logic is very human readable and follows what you described in the question.
answered Jan 23 at 12:35
fixer1234fixer1234
18.9k144982
This is pretty straightforward using the date functions.
Note that I'm in the US, so my dates are MM/DD/YYYY. I've added a December date so you can see how Excel intelligently handles the year if the month goes beyond 12. The formula in B1:
=DATE(YEAR(A1),MONTH(A1)+IF(DAY(A1)>6,2,1),21)
DATE() lets you specify a date by entering the year, month, and day. The target day is always the 21st day of the month. The year is taken as the year of the source date. All of the action happens with the month specification. We start with the month of the source date, then add either 1 or 2 months based on whether the source day is greater than 6. The logic is very human readable and follows what you described in the question.
answered Jan 23 at 12:35
fixer1234fixer1234
18.9k144982
answered Jan 23 at 12:35
fixer1234fixer1234
18.9k144982
answered Jan 23 at 12:35
fixer1234fixer1234
18.9k144982
answered Jan 23 at 12:35
fixer1234fixer1234
18.9k144982
18.9k144982
More elegant than mine.
– Mark Fitzgerald
Jan 23 at 12:38
@MarkFitzgerald, great minds think alike. :-) Our formulas are almost the same and it looks like we were both writing answers at the same time.
– fixer1234
Jan 23 at 12:41
add a comment |
More elegant than mine.
– Mark Fitzgerald
Jan 23 at 12:38
@MarkFitzgerald, great minds think alike. :-) Our formulas are almost the same and it looks like we were both writing answers at the same time.
– fixer1234
Jan 23 at 12:41
More elegant than mine.
– Mark Fitzgerald
Jan 23 at 12:38
More elegant than mine.
– Mark Fitzgerald
Jan 23 at 12:38
@MarkFitzgerald, great minds think alike. :-) Our formulas are almost the same and it looks like we were both writing answers at the same time.
– fixer1234
Jan 23 at 12:41
@MarkFitzgerald, great minds think alike. :-) Our formulas are almost the same and it looks like we were both writing answers at the same time.
– fixer1234
Jan 23 at 12:41
add a comment |
If your date was in Cell A1 then this formula in any other cell in row 1 will work:
=IF(DAY(A1)>6,DATE(YEAR(A1),MONTH(A1)+2,21),DATE(YEAR(A1),MONTH(A1)+1,21))
Copy down as required.
answered Jan 23 at 12:37
Mark FitzgeraldMark Fitzgerald
4021311
add a comment |
If your date was in Cell A1 then this formula in any other cell in row 1 will work:
=IF(DAY(A1)>6,DATE(YEAR(A1),MONTH(A1)+2,21),DATE(YEAR(A1),MONTH(A1)+1,21))
Copy down as required.
answered Jan 23 at 12:37
Mark FitzgeraldMark Fitzgerald
4021311
add a comment |
If your date was in Cell A1 then this formula in any other cell in row 1 will work:
=IF(DAY(A1)>6,DATE(YEAR(A1),MONTH(A1)+2,21),DATE(YEAR(A1),MONTH(A1)+1,21))
Copy down as required.
answered Jan 23 at 12:37
Mark FitzgeraldMark Fitzgerald
4021311
If your date was in Cell A1 then this formula in any other cell in row 1 will work:
=IF(DAY(A1)>6,DATE(YEAR(A1),MONTH(A1)+2,21),DATE(YEAR(A1),MONTH(A1)+1,21))
Copy down as required.
answered Jan 23 at 12:37
Mark FitzgeraldMark Fitzgerald
4021311
answered Jan 23 at 12:37
Mark FitzgeraldMark Fitzgerald
4021311
answered Jan 23 at 12:37
Mark FitzgeraldMark Fitzgerald
4021311
answered Jan 23 at 12:37
Mark FitzgeraldMark Fitzgerald
4021311
4021311
add a comment |
add a comment |
I prefer the IF function as its clearer to read and understand what is going on, but as an alternative you could use the following:
=DATE(YEAR(A1),MONTH(A1)+1+(DAY(A1)>6),21)
The (DAY(A1)>6) will return either TRUE or FALSE. When this is sent through a math operation, in this case addition, it will be converted to 1 for TRUE and 0 for FALSE.
answered Jan 23 at 13:18
Forward EdForward Ed
844214
add a comment |
I prefer the IF function as its clearer to read and understand what is going on, but as an alternative you could use the following:
=DATE(YEAR(A1),MONTH(A1)+1+(DAY(A1)>6),21)
The (DAY(A1)>6) will return either TRUE or FALSE. When this is sent through a math operation, in this case addition, it will be converted to 1 for TRUE and 0 for FALSE.
answered Jan 23 at 13:18
Forward EdForward Ed
844214
add a comment |
I prefer the IF function as its clearer to read and understand what is going on, but as an alternative you could use the following:
=DATE(YEAR(A1),MONTH(A1)+1+(DAY(A1)>6),21)
The (DAY(A1)>6) will return either TRUE or FALSE. When this is sent through a math operation, in this case addition, it will be converted to 1 for TRUE and 0 for FALSE.
answered Jan 23 at 13:18
Forward EdForward Ed
844214
I prefer the IF function as its clearer to read and understand what is going on, but as an alternative you could use the following:
=DATE(YEAR(A1),MONTH(A1)+1+(DAY(A1)>6),21)
The (DAY(A1)>6) will return either TRUE or FALSE. When this is sent through a math operation, in this case addition, it will be converted to 1 for TRUE and 0 for FALSE.
answered Jan 23 at 13:18
Forward EdForward Ed
844214
answered Jan 23 at 13:18
Forward EdForward Ed
844214
answered Jan 23 at 13:18
Forward EdForward Ed
844214
answered Jan 23 at 13:18
Forward EdForward Ed
844214
844214
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%2f1397433%2funique-excel-date-query-formula%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
