Formula to calculate days by month between two dates for future periods only
0
Struggling to come up with a formula that works for this (Data in A1 - E4)
StartDate EndDate Jan Feb Mar
12/4/2018 4/20/2019 31 28 31
9/26/2018 1/30/2019 30 0 0
1/1/2019 3/31/2019 31 28 31
1/1/2015 3/31/2019 155 141 155
IN Cell C2, formula is:
=SUMPRODUCT(--(TEXT(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),B2))),"mmm")=C$1))
What I am trying to do is exclude past dates from the sum, for example in the 4th row, the Jan/Feb/Mar should be 31/28/31 and not count previous years.
Any ideas where I am going wrong?
excel-formula
asked Jan 2 at 19:20
mrivardmrivard
31
add a comment |
0
Struggling to come up with a formula that works for this (Data in A1 - E4)
StartDate EndDate Jan Feb Mar
12/4/2018 4/20/2019 31 28 31
9/26/2018 1/30/2019 30 0 0
1/1/2019 3/31/2019 31 28 31
1/1/2015 3/31/2019 155 141 155
IN Cell C2, formula is:
=SUMPRODUCT(--(TEXT(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),B2))),"mmm")=C$1))
What I am trying to do is exclude past dates from the sum, for example in the 4th row, the Jan/Feb/Mar should be 31/28/31 and not count previous years.
Any ideas where I am going wrong?
excel-formula
asked Jan 2 at 19:20
mrivardmrivard
31
If you exclude past dates, today being the 2nd, would you not only have 30 for Jan or are you just wanting to exclude past years?
– Scott Craner
Jan 2 at 19:26
hi there, need to include all date ranges (some started a long time ago but continue well into the future, so only want to count ones in current year. I am needing to get 'days left' by month for 2019, so it's key to exclude the previous years from the count.
– mrivard
Jan 2 at 19:29
Okay, now that is different, you say future dates which would include other years after this one, or do you only want this year?
– Scott Craner
Jan 2 at 19:30
thanks!! appreciate the speedy help. Only needed current year.
– mrivard
Jan 2 at 19:39
add a comment |
0
0
0
Struggling to come up with a formula that works for this (Data in A1 - E4)
StartDate EndDate Jan Feb Mar
12/4/2018 4/20/2019 31 28 31
9/26/2018 1/30/2019 30 0 0
1/1/2019 3/31/2019 31 28 31
1/1/2015 3/31/2019 155 141 155
IN Cell C2, formula is:
=SUMPRODUCT(--(TEXT(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),B2))),"mmm")=C$1))
What I am trying to do is exclude past dates from the sum, for example in the 4th row, the Jan/Feb/Mar should be 31/28/31 and not count previous years.
Any ideas where I am going wrong?
excel-formula
asked Jan 2 at 19:20
mrivardmrivard
31
Struggling to come up with a formula that works for this (Data in A1 - E4)
StartDate EndDate Jan Feb Mar
12/4/2018 4/20/2019 31 28 31
9/26/2018 1/30/2019 30 0 0
1/1/2019 3/31/2019 31 28 31
1/1/2015 3/31/2019 155 141 155
IN Cell C2, formula is:
=SUMPRODUCT(--(TEXT(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),B2))),"mmm")=C$1))
What I am trying to do is exclude past dates from the sum, for example in the 4th row, the Jan/Feb/Mar should be 31/28/31 and not count previous years.
Any ideas where I am going wrong?
excel-formula
excel-formula
asked Jan 2 at 19:20
mrivardmrivard
31
asked Jan 2 at 19:20
mrivardmrivard
31
asked Jan 2 at 19:20
mrivardmrivard
31
asked Jan 2 at 19:20
mrivardmrivard
31
asked Jan 2 at 19:20
mrivardmrivard
31
31
If you exclude past dates, today being the 2nd, would you not only have 30 for Jan or are you just wanting to exclude past years?
– Scott Craner
Jan 2 at 19:26
hi there, need to include all date ranges (some started a long time ago but continue well into the future, so only want to count ones in current year. I am needing to get 'days left' by month for 2019, so it's key to exclude the previous years from the count.
– mrivard
Jan 2 at 19:29
Okay, now that is different, you say future dates which would include other years after this one, or do you only want this year?
– Scott Craner
Jan 2 at 19:30
thanks!! appreciate the speedy help. Only needed current year.
– mrivard
Jan 2 at 19:39
add a comment |
If you exclude past dates, today being the 2nd, would you not only have 30 for Jan or are you just wanting to exclude past years?
– Scott Craner
Jan 2 at 19:26
hi there, need to include all date ranges (some started a long time ago but continue well into the future, so only want to count ones in current year. I am needing to get 'days left' by month for 2019, so it's key to exclude the previous years from the count.
– mrivard
Jan 2 at 19:29
Okay, now that is different, you say future dates which would include other years after this one, or do you only want this year?
– Scott Craner
Jan 2 at 19:30
thanks!! appreciate the speedy help. Only needed current year.
– mrivard
Jan 2 at 19:39
If you exclude past dates, today being the 2nd, would you not only have 30 for Jan or are you just wanting to exclude past years?
– Scott Craner
Jan 2 at 19:26
If you exclude past dates, today being the 2nd, would you not only have 30 for Jan or are you just wanting to exclude past years?
– Scott Craner
Jan 2 at 19:26
hi there, need to include all date ranges (some started a long time ago but continue well into the future, so only want to count ones in current year. I am needing to get 'days left' by month for 2019, so it's key to exclude the previous years from the count.
– mrivard
Jan 2 at 19:29
hi there, need to include all date ranges (some started a long time ago but continue well into the future, so only want to count ones in current year. I am needing to get 'days left' by month for 2019, so it's key to exclude the previous years from the count.
– mrivard
Jan 2 at 19:29
Okay, now that is different, you say future dates which would include other years after this one, or do you only want this year?
– Scott Craner
Jan 2 at 19:30
Okay, now that is different, you say future dates which would include other years after this one, or do you only want this year?
– Scott Craner
Jan 2 at 19:30
thanks!! appreciate the speedy help. Only needed current year.
– mrivard
Jan 2 at 19:39
thanks!! appreciate the speedy help. Only needed current year.
– mrivard
Jan 2 at 19:39
add a comment |
1 Answer
1
active
oldest
votes
0
Add a chck for the year:
(YEAR(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),$B2))))=YEAR(TODAY()))
and just multiply them in the SUMPRODUCT:
=SUMPRODUCT((YEAR(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),$B2))))>=YEAR(TODAY()))*(TEXT(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),$B2))),"mmm")=C$1))
One Note:
INDIRECT is Volatile and will re-calc each time Excel re-calcs. I prefer to replace them with INDEX:
=SUMPRODUCT((YEAR(ROW(INDEX($A:$A,$A2) :INDEX($A:$A, IF($B2="",TODAY(),$B2))))>=YEAR(TODAY()))*(TEXT(ROW(INDEX($A:$A,$A2) :INDEX($A:$A, IF($B2="",TODAY(),$B2))),"mmm")=C$1))
answered Jan 2 at 19:31
Scott CranerScott Craner
92.6k82652
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
});
}
});
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%2fstackoverflow.com%2fquestions%2f54012017%2fformula-to-calculate-days-by-month-between-two-dates-for-future-periods-only%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
0
Add a chck for the year:
(YEAR(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),$B2))))=YEAR(TODAY()))
and just multiply them in the SUMPRODUCT:
=SUMPRODUCT((YEAR(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),$B2))))>=YEAR(TODAY()))*(TEXT(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),$B2))),"mmm")=C$1))
One Note:
INDIRECT is Volatile and will re-calc each time Excel re-calcs. I prefer to replace them with INDEX:
=SUMPRODUCT((YEAR(ROW(INDEX($A:$A,$A2) :INDEX($A:$A, IF($B2="",TODAY(),$B2))))>=YEAR(TODAY()))*(TEXT(ROW(INDEX($A:$A,$A2) :INDEX($A:$A, IF($B2="",TODAY(),$B2))),"mmm")=C$1))
answered Jan 2 at 19:31
Scott CranerScott Craner
92.6k82652
add a comment |
0
Add a chck for the year:
(YEAR(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),$B2))))=YEAR(TODAY()))
and just multiply them in the SUMPRODUCT:
=SUMPRODUCT((YEAR(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),$B2))))>=YEAR(TODAY()))*(TEXT(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),$B2))),"mmm")=C$1))
One Note:
INDIRECT is Volatile and will re-calc each time Excel re-calcs. I prefer to replace them with INDEX:
=SUMPRODUCT((YEAR(ROW(INDEX($A:$A,$A2) :INDEX($A:$A, IF($B2="",TODAY(),$B2))))>=YEAR(TODAY()))*(TEXT(ROW(INDEX($A:$A,$A2) :INDEX($A:$A, IF($B2="",TODAY(),$B2))),"mmm")=C$1))
answered Jan 2 at 19:31
Scott CranerScott Craner
92.6k82652
add a comment |
0
0
0
Add a chck for the year:
(YEAR(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),$B2))))=YEAR(TODAY()))
and just multiply them in the SUMPRODUCT:
=SUMPRODUCT((YEAR(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),$B2))))>=YEAR(TODAY()))*(TEXT(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),$B2))),"mmm")=C$1))
One Note:
INDIRECT is Volatile and will re-calc each time Excel re-calcs. I prefer to replace them with INDEX:
=SUMPRODUCT((YEAR(ROW(INDEX($A:$A,$A2) :INDEX($A:$A, IF($B2="",TODAY(),$B2))))>=YEAR(TODAY()))*(TEXT(ROW(INDEX($A:$A,$A2) :INDEX($A:$A, IF($B2="",TODAY(),$B2))),"mmm")=C$1))
answered Jan 2 at 19:31
Scott CranerScott Craner
92.6k82652
Add a chck for the year:
(YEAR(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),$B2))))=YEAR(TODAY()))
and just multiply them in the SUMPRODUCT:
=SUMPRODUCT((YEAR(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),$B2))))>=YEAR(TODAY()))*(TEXT(ROW(INDIRECT($A2 & ":" & IF($B2="",TODAY(),$B2))),"mmm")=C$1))
One Note:
INDIRECT is Volatile and will re-calc each time Excel re-calcs. I prefer to replace them with INDEX:
=SUMPRODUCT((YEAR(ROW(INDEX($A:$A,$A2) :INDEX($A:$A, IF($B2="",TODAY(),$B2))))>=YEAR(TODAY()))*(TEXT(ROW(INDEX($A:$A,$A2) :INDEX($A:$A, IF($B2="",TODAY(),$B2))),"mmm")=C$1))
answered Jan 2 at 19:31
Scott CranerScott Craner
92.6k82652
edited Jan 2 at 19:38
answered Jan 2 at 19:31
Scott CranerScott Craner
92.6k82652
answered Jan 2 at 19:31
Scott CranerScott Craner
92.6k82652
answered Jan 2 at 19:31
Scott CranerScott Craner
92.6k82652
92.6k82652
add a comment |
add a comment |
draft saved
draft discarded
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.
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%2fstackoverflow.com%2fquestions%2f54012017%2fformula-to-calculate-days-by-month-between-two-dates-for-future-periods-only%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
If you exclude past dates, today being the 2nd, would you not only have 30 for Jan or are you just wanting to exclude past years?
– Scott Craner
Jan 2 at 19:26
hi there, need to include all date ranges (some started a long time ago but continue well into the future, so only want to count ones in current year. I am needing to get 'days left' by month for 2019, so it's key to exclude the previous years from the count.
– mrivard
Jan 2 at 19:29
Okay, now that is different, you say future dates which would include other years after this one, or do you only want this year?
– Scott Craner
Jan 2 at 19:30
thanks!! appreciate the speedy help. Only needed current year.
– mrivard
Jan 2 at 19:39