Calculate the number of days per year between two different dates Excel
I have an Excel data sheet which contains about 200 rows, in each row, there is a Start date and End date. I need to calculate the difference between the dates but per year.
suppose the start date is 3/3/2017 and the End date is 3/2/2019, I need to calculate the number of days in 2017, 2018 and 2019 between both dates.
microsoft-excel
asked Jan 20 at 18:03
user987600user987600
1
add a comment |
I have an Excel data sheet which contains about 200 rows, in each row, there is a Start date and End date. I need to calculate the difference between the dates but per year.
suppose the start date is 3/3/2017 and the End date is 3/2/2019, I need to calculate the number of days in 2017, 2018 and 2019 between both dates.
microsoft-excel
asked Jan 20 at 18:03
user987600user987600
1
1
You want the total number of days between the start and end or 3 different numbers representing each year in the range?
– DavidPostill♦
Jan 20 at 18:08
Welcome to Super User! You can freely edit your own posts but for your protection, this must be done under the original user account. It looks like you have created a second account, which will also interfere with your ability to comment within your thread and to accept an answer. See Merge my accounts to get your accounts merged, which will solve the problem.
– fixer1234
Jan 21 at 3:32
add a comment |
I have an Excel data sheet which contains about 200 rows, in each row, there is a Start date and End date. I need to calculate the difference between the dates but per year.
suppose the start date is 3/3/2017 and the End date is 3/2/2019, I need to calculate the number of days in 2017, 2018 and 2019 between both dates.
microsoft-excel
asked Jan 20 at 18:03
user987600user987600
1
I have an Excel data sheet which contains about 200 rows, in each row, there is a Start date and End date. I need to calculate the difference between the dates but per year.
suppose the start date is 3/3/2017 and the End date is 3/2/2019, I need to calculate the number of days in 2017, 2018 and 2019 between both dates.
microsoft-excel
microsoft-excel
asked Jan 20 at 18:03
user987600user987600
1
asked Jan 20 at 18:03
user987600user987600
1
asked Jan 20 at 18:03
user987600user987600
1
asked Jan 20 at 18:03
user987600user987600
1
asked Jan 20 at 18:03
user987600user987600
1
1
1
You want the total number of days between the start and end or 3 different numbers representing each year in the range?
– DavidPostill♦
Jan 20 at 18:08
Welcome to Super User! You can freely edit your own posts but for your protection, this must be done under the original user account. It looks like you have created a second account, which will also interfere with your ability to comment within your thread and to accept an answer. See Merge my accounts to get your accounts merged, which will solve the problem.
– fixer1234
Jan 21 at 3:32
add a comment |
1
You want the total number of days between the start and end or 3 different numbers representing each year in the range?
– DavidPostill♦
Jan 20 at 18:08
Welcome to Super User! You can freely edit your own posts but for your protection, this must be done under the original user account. It looks like you have created a second account, which will also interfere with your ability to comment within your thread and to accept an answer. See Merge my accounts to get your accounts merged, which will solve the problem.
– fixer1234
Jan 21 at 3:32
1
1
You want the total number of days between the start and end or 3 different numbers representing each year in the range?
– DavidPostill♦
Jan 20 at 18:08
You want the total number of days between the start and end or 3 different numbers representing each year in the range?
– DavidPostill♦
Jan 20 at 18:08
Welcome to Super User! You can freely edit your own posts but for your protection, this must be done under the original user account. It looks like you have created a second account, which will also interfere with your ability to comment within your thread and to accept an answer. See Merge my accounts to get your accounts merged, which will solve the problem.
– fixer1234
Jan 21 at 3:32
Welcome to Super User! You can freely edit your own posts but for your protection, this must be done under the original user account. It looks like you have created a second account, which will also interfere with your ability to comment within your thread and to accept an answer. See Merge my accounts to get your accounts merged, which will solve the problem.
– fixer1234
Jan 21 at 3:32
add a comment |
1 Answer
1
active
oldest
votes
This formula should work up to 25 November 4770, by which time Excel may have been superseded by something else.
=SUMPRODUCT(--(YEAR(ROW(INDIRECT(StartDt &":"&endDt)))=C$1))
where C1 contains the year in question.
For example:
However, you write you want to know the "difference between the dates". That phraseology usually indicates that you don't want to count the first date. The formula counts all of the dates.
If you really want the "difference" and not the number of days from Start to End, then add one to the start date in the formula:
=SUMPRODUCT(--(YEAR(ROW(INDIRECT(StartDt+1 &":"&endDt)))=C$1))
Edit: (provoked by @fixer1234) To understand how this is working, you need to understand that
- dates are stored in Excel as serial numbers with, usually,
1-jan-1900equal to 1.
ROW(INDIRECT(n:m))is a way of returning an array of numbers equal to the row numbers represented bynandm
- The
YEARfunction then returns the the equivalent year from each of those values, with we compare with the year at the top of the relevant column, generating an array ofTRUE;FALSE. - Since, in Excel,
--TRUE=1,SUMPRODUCTwill effectively return theSUMof all the dates that are in the year at the top of the column. - The date limitation I mentioned is because the are only
2^20rows in Excel. It could be overcome by applying an offset to the dates within theROW(INDIRECT(…construct, and then adding that back after, if the dates involved are later than 11/25/4770.
answered Jan 20 at 20:38
Ron RosenfeldRon Rosenfeld
2,0242611
Clever, but suggest changing the "named ranges" to explicit ranges so that this solution works in spreadsheet apps other than Excel. e.g., C2:=SUMPRODUCT(--(YEAR(ROW(INDIRECT($A2&":"&$B2)))=C$1)). That was the only way I could get this to work in LO Calc. It would also be helpful to add a few sentences to explain how it works using imaginary rows.
– fixer1234
Jan 20 at 21:28
I agree that an explanation of the algorithm might be useful, although I think readers are better served, with non-complex algorithms, by making the effort to figure it out themselves. I disagree about avoiding "named ranges". I feel they lend clarity to the formula and make future maintenance easier. I do include caveats if there are applications listed in the tags in which the formula might not work, or with which I am not familiar. However, that was not the case here.
– Ron Rosenfeld
Jan 21 at 12:17
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%2f1396353%2fcalculate-the-number-of-days-per-year-between-two-different-dates-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
This formula should work up to 25 November 4770, by which time Excel may have been superseded by something else.
=SUMPRODUCT(--(YEAR(ROW(INDIRECT(StartDt &":"&endDt)))=C$1))
where C1 contains the year in question.
For example:
However, you write you want to know the "difference between the dates". That phraseology usually indicates that you don't want to count the first date. The formula counts all of the dates.
If you really want the "difference" and not the number of days from Start to End, then add one to the start date in the formula:
=SUMPRODUCT(--(YEAR(ROW(INDIRECT(StartDt+1 &":"&endDt)))=C$1))
Edit: (provoked by @fixer1234) To understand how this is working, you need to understand that
- dates are stored in Excel as serial numbers with, usually,
1-jan-1900equal to 1.
ROW(INDIRECT(n:m))is a way of returning an array of numbers equal to the row numbers represented bynandm
- The
YEARfunction then returns the the equivalent year from each of those values, with we compare with the year at the top of the relevant column, generating an array ofTRUE;FALSE. - Since, in Excel,
--TRUE=1,SUMPRODUCTwill effectively return theSUMof all the dates that are in the year at the top of the column. - The date limitation I mentioned is because the are only
2^20rows in Excel. It could be overcome by applying an offset to the dates within theROW(INDIRECT(…construct, and then adding that back after, if the dates involved are later than 11/25/4770.
answered Jan 20 at 20:38
Ron RosenfeldRon Rosenfeld
2,0242611
Clever, but suggest changing the "named ranges" to explicit ranges so that this solution works in spreadsheet apps other than Excel. e.g., C2:=SUMPRODUCT(--(YEAR(ROW(INDIRECT($A2&":"&$B2)))=C$1)). That was the only way I could get this to work in LO Calc. It would also be helpful to add a few sentences to explain how it works using imaginary rows.
– fixer1234
Jan 20 at 21:28
I agree that an explanation of the algorithm might be useful, although I think readers are better served, with non-complex algorithms, by making the effort to figure it out themselves. I disagree about avoiding "named ranges". I feel they lend clarity to the formula and make future maintenance easier. I do include caveats if there are applications listed in the tags in which the formula might not work, or with which I am not familiar. However, that was not the case here.
– Ron Rosenfeld
Jan 21 at 12:17
add a comment |
This formula should work up to 25 November 4770, by which time Excel may have been superseded by something else.
=SUMPRODUCT(--(YEAR(ROW(INDIRECT(StartDt &":"&endDt)))=C$1))
where C1 contains the year in question.
For example:
However, you write you want to know the "difference between the dates". That phraseology usually indicates that you don't want to count the first date. The formula counts all of the dates.
If you really want the "difference" and not the number of days from Start to End, then add one to the start date in the formula:
=SUMPRODUCT(--(YEAR(ROW(INDIRECT(StartDt+1 &":"&endDt)))=C$1))
Edit: (provoked by @fixer1234) To understand how this is working, you need to understand that
- dates are stored in Excel as serial numbers with, usually,
1-jan-1900equal to 1.
ROW(INDIRECT(n:m))is a way of returning an array of numbers equal to the row numbers represented bynandm
- The
YEARfunction then returns the the equivalent year from each of those values, with we compare with the year at the top of the relevant column, generating an array ofTRUE;FALSE. - Since, in Excel,
--TRUE=1,SUMPRODUCTwill effectively return theSUMof all the dates that are in the year at the top of the column. - The date limitation I mentioned is because the are only
2^20rows in Excel. It could be overcome by applying an offset to the dates within theROW(INDIRECT(…construct, and then adding that back after, if the dates involved are later than 11/25/4770.
answered Jan 20 at 20:38
Ron RosenfeldRon Rosenfeld
2,0242611
Clever, but suggest changing the "named ranges" to explicit ranges so that this solution works in spreadsheet apps other than Excel. e.g., C2:=SUMPRODUCT(--(YEAR(ROW(INDIRECT($A2&":"&$B2)))=C$1)). That was the only way I could get this to work in LO Calc. It would also be helpful to add a few sentences to explain how it works using imaginary rows.
– fixer1234
Jan 20 at 21:28
I agree that an explanation of the algorithm might be useful, although I think readers are better served, with non-complex algorithms, by making the effort to figure it out themselves. I disagree about avoiding "named ranges". I feel they lend clarity to the formula and make future maintenance easier. I do include caveats if there are applications listed in the tags in which the formula might not work, or with which I am not familiar. However, that was not the case here.
– Ron Rosenfeld
Jan 21 at 12:17
add a comment |
This formula should work up to 25 November 4770, by which time Excel may have been superseded by something else.
=SUMPRODUCT(--(YEAR(ROW(INDIRECT(StartDt &":"&endDt)))=C$1))
where C1 contains the year in question.
For example:
However, you write you want to know the "difference between the dates". That phraseology usually indicates that you don't want to count the first date. The formula counts all of the dates.
If you really want the "difference" and not the number of days from Start to End, then add one to the start date in the formula:
=SUMPRODUCT(--(YEAR(ROW(INDIRECT(StartDt+1 &":"&endDt)))=C$1))
Edit: (provoked by @fixer1234) To understand how this is working, you need to understand that
- dates are stored in Excel as serial numbers with, usually,
1-jan-1900equal to 1.
ROW(INDIRECT(n:m))is a way of returning an array of numbers equal to the row numbers represented bynandm
- The
YEARfunction then returns the the equivalent year from each of those values, with we compare with the year at the top of the relevant column, generating an array ofTRUE;FALSE. - Since, in Excel,
--TRUE=1,SUMPRODUCTwill effectively return theSUMof all the dates that are in the year at the top of the column. - The date limitation I mentioned is because the are only
2^20rows in Excel. It could be overcome by applying an offset to the dates within theROW(INDIRECT(…construct, and then adding that back after, if the dates involved are later than 11/25/4770.
answered Jan 20 at 20:38
Ron RosenfeldRon Rosenfeld
2,0242611
This formula should work up to 25 November 4770, by which time Excel may have been superseded by something else.
=SUMPRODUCT(--(YEAR(ROW(INDIRECT(StartDt &":"&endDt)))=C$1))
where C1 contains the year in question.
For example:
However, you write you want to know the "difference between the dates". That phraseology usually indicates that you don't want to count the first date. The formula counts all of the dates.
If you really want the "difference" and not the number of days from Start to End, then add one to the start date in the formula:
=SUMPRODUCT(--(YEAR(ROW(INDIRECT(StartDt+1 &":"&endDt)))=C$1))
Edit: (provoked by @fixer1234) To understand how this is working, you need to understand that
- dates are stored in Excel as serial numbers with, usually,
1-jan-1900equal to 1.
ROW(INDIRECT(n:m))is a way of returning an array of numbers equal to the row numbers represented bynandm
- The
YEARfunction then returns the the equivalent year from each of those values, with we compare with the year at the top of the relevant column, generating an array ofTRUE;FALSE. - Since, in Excel,
--TRUE=1,SUMPRODUCTwill effectively return theSUMof all the dates that are in the year at the top of the column. - The date limitation I mentioned is because the are only
2^20rows in Excel. It could be overcome by applying an offset to the dates within theROW(INDIRECT(…construct, and then adding that back after, if the dates involved are later than 11/25/4770.
answered Jan 20 at 20:38
Ron RosenfeldRon Rosenfeld
2,0242611
edited Jan 21 at 12:35
answered Jan 20 at 20:38
Ron RosenfeldRon Rosenfeld
2,0242611
answered Jan 20 at 20:38
Ron RosenfeldRon Rosenfeld
2,0242611
answered Jan 20 at 20:38
Ron RosenfeldRon Rosenfeld
2,0242611
2,0242611
Clever, but suggest changing the "named ranges" to explicit ranges so that this solution works in spreadsheet apps other than Excel. e.g., C2:=SUMPRODUCT(--(YEAR(ROW(INDIRECT($A2&":"&$B2)))=C$1)). That was the only way I could get this to work in LO Calc. It would also be helpful to add a few sentences to explain how it works using imaginary rows.
– fixer1234
Jan 20 at 21:28
I agree that an explanation of the algorithm might be useful, although I think readers are better served, with non-complex algorithms, by making the effort to figure it out themselves. I disagree about avoiding "named ranges". I feel they lend clarity to the formula and make future maintenance easier. I do include caveats if there are applications listed in the tags in which the formula might not work, or with which I am not familiar. However, that was not the case here.
– Ron Rosenfeld
Jan 21 at 12:17
add a comment |
Clever, but suggest changing the "named ranges" to explicit ranges so that this solution works in spreadsheet apps other than Excel. e.g., C2:=SUMPRODUCT(--(YEAR(ROW(INDIRECT($A2&":"&$B2)))=C$1)). That was the only way I could get this to work in LO Calc. It would also be helpful to add a few sentences to explain how it works using imaginary rows.
– fixer1234
Jan 20 at 21:28
I agree that an explanation of the algorithm might be useful, although I think readers are better served, with non-complex algorithms, by making the effort to figure it out themselves. I disagree about avoiding "named ranges". I feel they lend clarity to the formula and make future maintenance easier. I do include caveats if there are applications listed in the tags in which the formula might not work, or with which I am not familiar. However, that was not the case here.
– Ron Rosenfeld
Jan 21 at 12:17
Clever, but suggest changing the "named ranges" to explicit ranges so that this solution works in spreadsheet apps other than Excel. e.g., C2:
=SUMPRODUCT(--(YEAR(ROW(INDIRECT($A2&":"&$B2)))=C$1)). That was the only way I could get this to work in LO Calc. It would also be helpful to add a few sentences to explain how it works using imaginary rows.– fixer1234
Jan 20 at 21:28
Clever, but suggest changing the "named ranges" to explicit ranges so that this solution works in spreadsheet apps other than Excel. e.g., C2:
=SUMPRODUCT(--(YEAR(ROW(INDIRECT($A2&":"&$B2)))=C$1)). That was the only way I could get this to work in LO Calc. It would also be helpful to add a few sentences to explain how it works using imaginary rows.– fixer1234
Jan 20 at 21:28
I agree that an explanation of the algorithm might be useful, although I think readers are better served, with non-complex algorithms, by making the effort to figure it out themselves. I disagree about avoiding "named ranges". I feel they lend clarity to the formula and make future maintenance easier. I do include caveats if there are applications listed in the tags in which the formula might not work, or with which I am not familiar. However, that was not the case here.
– Ron Rosenfeld
Jan 21 at 12:17
I agree that an explanation of the algorithm might be useful, although I think readers are better served, with non-complex algorithms, by making the effort to figure it out themselves. I disagree about avoiding "named ranges". I feel they lend clarity to the formula and make future maintenance easier. I do include caveats if there are applications listed in the tags in which the formula might not work, or with which I am not familiar. However, that was not the case here.
– Ron Rosenfeld
Jan 21 at 12:17
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%2f1396353%2fcalculate-the-number-of-days-per-year-between-two-different-dates-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
1
You want the total number of days between the start and end or 3 different numbers representing each year in the range?
– DavidPostill♦
Jan 20 at 18:08
Welcome to Super User! You can freely edit your own posts but for your protection, this must be done under the original user account. It looks like you have created a second account, which will also interfere with your ability to comment within your thread and to accept an answer. See Merge my accounts to get your accounts merged, which will solve the problem.
– fixer1234
Jan 21 at 3:32