Find Max fiscal year data in excel
Hi I am trying to use MAX(range) on a column in a table of dates. Formatted as 2016-17, 2017-18, and so on. Any idea on how to do this with formulas? Thanks in advance!
excel-formula excel-2010
asked Nov 23 '18 at 18:03
User
1259
|
show 4 more comments
Hi I am trying to use MAX(range) on a column in a table of dates. Formatted as 2016-17, 2017-18, and so on. Any idea on how to do this with formulas? Thanks in advance!
excel-formula excel-2010
asked Nov 23 '18 at 18:03
User
1259
Are they dates that are formatted to appear like that (like actual dates in the cell) or are these strings that are in that format?
– JNevill
Nov 23 '18 at 18:09
No these are strings imputed in this format.
– User
Nov 23 '18 at 18:11
You could do=DateValue(A1 & "-01")in a new column and then take the max of that. I think the important part is getting those converted to a real date since you can't take themax()of a string.
– JNevill
Nov 23 '18 at 18:17
What does "-01" do?
– User
Nov 23 '18 at 18:22
That concatenates the-01to the end of the date string like2017-08into2017-08-01(august 1st, 2018). TheDateValue()then converts that full date string into an actual excel date (stored as a 5 digit number). It's pretty common when just wanting to deal withYYYY-MMthat the actual date stored under the hood is the 1st of the month, so this fits that fairly common whole thing.
– JNevill
Nov 23 '18 at 18:27
|
show 4 more comments
Hi I am trying to use MAX(range) on a column in a table of dates. Formatted as 2016-17, 2017-18, and so on. Any idea on how to do this with formulas? Thanks in advance!
excel-formula excel-2010
asked Nov 23 '18 at 18:03
User
1259
Hi I am trying to use MAX(range) on a column in a table of dates. Formatted as 2016-17, 2017-18, and so on. Any idea on how to do this with formulas? Thanks in advance!
excel-formula excel-2010
excel-formula excel-2010
asked Nov 23 '18 at 18:03
User
1259
asked Nov 23 '18 at 18:03
User
1259
asked Nov 23 '18 at 18:03
User
1259
asked Nov 23 '18 at 18:03
User
1259
asked Nov 23 '18 at 18:03
User
1259
1259
Are they dates that are formatted to appear like that (like actual dates in the cell) or are these strings that are in that format?
– JNevill
Nov 23 '18 at 18:09
No these are strings imputed in this format.
– User
Nov 23 '18 at 18:11
You could do=DateValue(A1 & "-01")in a new column and then take the max of that. I think the important part is getting those converted to a real date since you can't take themax()of a string.
– JNevill
Nov 23 '18 at 18:17
What does "-01" do?
– User
Nov 23 '18 at 18:22
That concatenates the-01to the end of the date string like2017-08into2017-08-01(august 1st, 2018). TheDateValue()then converts that full date string into an actual excel date (stored as a 5 digit number). It's pretty common when just wanting to deal withYYYY-MMthat the actual date stored under the hood is the 1st of the month, so this fits that fairly common whole thing.
– JNevill
Nov 23 '18 at 18:27
|
show 4 more comments
Are they dates that are formatted to appear like that (like actual dates in the cell) or are these strings that are in that format?
– JNevill
Nov 23 '18 at 18:09
No these are strings imputed in this format.
– User
Nov 23 '18 at 18:11
You could do=DateValue(A1 & "-01")in a new column and then take the max of that. I think the important part is getting those converted to a real date since you can't take themax()of a string.
– JNevill
Nov 23 '18 at 18:17
What does "-01" do?
– User
Nov 23 '18 at 18:22
That concatenates the-01to the end of the date string like2017-08into2017-08-01(august 1st, 2018). TheDateValue()then converts that full date string into an actual excel date (stored as a 5 digit number). It's pretty common when just wanting to deal withYYYY-MMthat the actual date stored under the hood is the 1st of the month, so this fits that fairly common whole thing.
– JNevill
Nov 23 '18 at 18:27
Are they dates that are formatted to appear like that (like actual dates in the cell) or are these strings that are in that format?
– JNevill
Nov 23 '18 at 18:09
Are they dates that are formatted to appear like that (like actual dates in the cell) or are these strings that are in that format?
– JNevill
Nov 23 '18 at 18:09
No these are strings imputed in this format.
– User
Nov 23 '18 at 18:11
No these are strings imputed in this format.
– User
Nov 23 '18 at 18:11
You could do
=DateValue(A1 & "-01") in a new column and then take the max of that. I think the important part is getting those converted to a real date since you can't take the max() of a string.– JNevill
Nov 23 '18 at 18:17
You could do
=DateValue(A1 & "-01") in a new column and then take the max of that. I think the important part is getting those converted to a real date since you can't take the max() of a string.– JNevill
Nov 23 '18 at 18:17
What does "-01" do?
– User
Nov 23 '18 at 18:22
What does "-01" do?
– User
Nov 23 '18 at 18:22
That concatenates the
-01 to the end of the date string like 2017-08 into 2017-08-01 (august 1st, 2018). The DateValue() then converts that full date string into an actual excel date (stored as a 5 digit number). It's pretty common when just wanting to deal with YYYY-MM that the actual date stored under the hood is the 1st of the month, so this fits that fairly common whole thing.– JNevill
Nov 23 '18 at 18:27
That concatenates the
-01 to the end of the date string like 2017-08 into 2017-08-01 (august 1st, 2018). The DateValue() then converts that full date string into an actual excel date (stored as a 5 digit number). It's pretty common when just wanting to deal with YYYY-MM that the actual date stored under the hood is the 1st of the month, so this fits that fairly common whole thing.– JNevill
Nov 23 '18 at 18:27
|
show 4 more comments
2 Answers
2
active
oldest
votes
You can use the formula as below
Formula in cell D1 is an array formula which is to be entered using Ctrl+Shift+Enter.
it is
=INDEX($A$2:$A$5,MATCH(MAX( --( (RIGHT($A$2:$A$5,2))) ),--(RIGHT($A$2:$A$5,2))))
answered Nov 24 '18 at 13:48
usmanhaq
1,123128
add a comment |
In order to achieve the above, Data Validation and 1 formula were used.
In the cell for the date you are looking for, use data validation. Set the validation to use a list, and then select the date header row as the list. In the example above it was set to B1:C1. Then you just select the date you want to find the max of from the pull down that appear for the cell. This is completely optional way of selecting the date that will save you typos or errors of looking for something that is not a match for a header in your data.
Finding the MAX. use the following formula which is a combination of MAX, INDEX and MATCH. You will match the date your are looking for in G1 to the appropriate column header in B1:C1. Index will return the entire column that matches due to the 0. MAX will then turn around and find the maximum number from that information.
=MAX(INDEX(B2:C4,0,MATCH(G1,B1:C1,0)))
Adjust and lock cell ranges to suit your data.
OPTIONAL DATE SELECTION
If you wind up having to select a column based on an actual date. you will need to know the the month and day of year that the fiscal year start or end on. Based on being before or after that date, you will convert the date to a string that matches the format of your column headers and look up that way.
Example
Fiscal Year Starts April 1st
Date you want to look up is 2017/10/23 (H1)
=IF(H1<Date(Year(H1),4,1),(YEAR(H1)-1)&"-"&RIGHT(YEAR(H1),2),YEAR(H1)&"-"&RIGHT(YEAR(H1)+1,2))
The above could be used in lieu of the data validation method described earlier and would be placed in G1.
answered Nov 23 '18 at 19:48
Forward Ed
6,51411336
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
});
}
});
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%2f53451217%2ffind-max-fiscal-year-data-in-excel%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
You can use the formula as below
Formula in cell D1 is an array formula which is to be entered using Ctrl+Shift+Enter.
it is
=INDEX($A$2:$A$5,MATCH(MAX( --( (RIGHT($A$2:$A$5,2))) ),--(RIGHT($A$2:$A$5,2))))
answered Nov 24 '18 at 13:48
usmanhaq
1,123128
add a comment |
You can use the formula as below
Formula in cell D1 is an array formula which is to be entered using Ctrl+Shift+Enter.
it is
=INDEX($A$2:$A$5,MATCH(MAX( --( (RIGHT($A$2:$A$5,2))) ),--(RIGHT($A$2:$A$5,2))))
answered Nov 24 '18 at 13:48
usmanhaq
1,123128
add a comment |
You can use the formula as below
Formula in cell D1 is an array formula which is to be entered using Ctrl+Shift+Enter.
it is
=INDEX($A$2:$A$5,MATCH(MAX( --( (RIGHT($A$2:$A$5,2))) ),--(RIGHT($A$2:$A$5,2))))
answered Nov 24 '18 at 13:48
usmanhaq
1,123128
You can use the formula as below
Formula in cell D1 is an array formula which is to be entered using Ctrl+Shift+Enter.
it is
=INDEX($A$2:$A$5,MATCH(MAX( --( (RIGHT($A$2:$A$5,2))) ),--(RIGHT($A$2:$A$5,2))))
answered Nov 24 '18 at 13:48
usmanhaq
1,123128
edited Nov 24 '18 at 13:55
answered Nov 24 '18 at 13:48
usmanhaq
1,123128
answered Nov 24 '18 at 13:48
usmanhaq
1,123128
answered Nov 24 '18 at 13:48
usmanhaq
1,123128
1,123128
add a comment |
add a comment |
In order to achieve the above, Data Validation and 1 formula were used.
In the cell for the date you are looking for, use data validation. Set the validation to use a list, and then select the date header row as the list. In the example above it was set to B1:C1. Then you just select the date you want to find the max of from the pull down that appear for the cell. This is completely optional way of selecting the date that will save you typos or errors of looking for something that is not a match for a header in your data.
Finding the MAX. use the following formula which is a combination of MAX, INDEX and MATCH. You will match the date your are looking for in G1 to the appropriate column header in B1:C1. Index will return the entire column that matches due to the 0. MAX will then turn around and find the maximum number from that information.
=MAX(INDEX(B2:C4,0,MATCH(G1,B1:C1,0)))
Adjust and lock cell ranges to suit your data.
OPTIONAL DATE SELECTION
If you wind up having to select a column based on an actual date. you will need to know the the month and day of year that the fiscal year start or end on. Based on being before or after that date, you will convert the date to a string that matches the format of your column headers and look up that way.
Example
Fiscal Year Starts April 1st
Date you want to look up is 2017/10/23 (H1)
=IF(H1<Date(Year(H1),4,1),(YEAR(H1)-1)&"-"&RIGHT(YEAR(H1),2),YEAR(H1)&"-"&RIGHT(YEAR(H1)+1,2))
The above could be used in lieu of the data validation method described earlier and would be placed in G1.
answered Nov 23 '18 at 19:48
Forward Ed
6,51411336
add a comment |
In order to achieve the above, Data Validation and 1 formula were used.
In the cell for the date you are looking for, use data validation. Set the validation to use a list, and then select the date header row as the list. In the example above it was set to B1:C1. Then you just select the date you want to find the max of from the pull down that appear for the cell. This is completely optional way of selecting the date that will save you typos or errors of looking for something that is not a match for a header in your data.
Finding the MAX. use the following formula which is a combination of MAX, INDEX and MATCH. You will match the date your are looking for in G1 to the appropriate column header in B1:C1. Index will return the entire column that matches due to the 0. MAX will then turn around and find the maximum number from that information.
=MAX(INDEX(B2:C4,0,MATCH(G1,B1:C1,0)))
Adjust and lock cell ranges to suit your data.
OPTIONAL DATE SELECTION
If you wind up having to select a column based on an actual date. you will need to know the the month and day of year that the fiscal year start or end on. Based on being before or after that date, you will convert the date to a string that matches the format of your column headers and look up that way.
Example
Fiscal Year Starts April 1st
Date you want to look up is 2017/10/23 (H1)
=IF(H1<Date(Year(H1),4,1),(YEAR(H1)-1)&"-"&RIGHT(YEAR(H1),2),YEAR(H1)&"-"&RIGHT(YEAR(H1)+1,2))
The above could be used in lieu of the data validation method described earlier and would be placed in G1.
answered Nov 23 '18 at 19:48
Forward Ed
6,51411336
add a comment |
In order to achieve the above, Data Validation and 1 formula were used.
In the cell for the date you are looking for, use data validation. Set the validation to use a list, and then select the date header row as the list. In the example above it was set to B1:C1. Then you just select the date you want to find the max of from the pull down that appear for the cell. This is completely optional way of selecting the date that will save you typos or errors of looking for something that is not a match for a header in your data.
Finding the MAX. use the following formula which is a combination of MAX, INDEX and MATCH. You will match the date your are looking for in G1 to the appropriate column header in B1:C1. Index will return the entire column that matches due to the 0. MAX will then turn around and find the maximum number from that information.
=MAX(INDEX(B2:C4,0,MATCH(G1,B1:C1,0)))
Adjust and lock cell ranges to suit your data.
OPTIONAL DATE SELECTION
If you wind up having to select a column based on an actual date. you will need to know the the month and day of year that the fiscal year start or end on. Based on being before or after that date, you will convert the date to a string that matches the format of your column headers and look up that way.
Example
Fiscal Year Starts April 1st
Date you want to look up is 2017/10/23 (H1)
=IF(H1<Date(Year(H1),4,1),(YEAR(H1)-1)&"-"&RIGHT(YEAR(H1),2),YEAR(H1)&"-"&RIGHT(YEAR(H1)+1,2))
The above could be used in lieu of the data validation method described earlier and would be placed in G1.
answered Nov 23 '18 at 19:48
Forward Ed
6,51411336
In order to achieve the above, Data Validation and 1 formula were used.
In the cell for the date you are looking for, use data validation. Set the validation to use a list, and then select the date header row as the list. In the example above it was set to B1:C1. Then you just select the date you want to find the max of from the pull down that appear for the cell. This is completely optional way of selecting the date that will save you typos or errors of looking for something that is not a match for a header in your data.
Finding the MAX. use the following formula which is a combination of MAX, INDEX and MATCH. You will match the date your are looking for in G1 to the appropriate column header in B1:C1. Index will return the entire column that matches due to the 0. MAX will then turn around and find the maximum number from that information.
=MAX(INDEX(B2:C4,0,MATCH(G1,B1:C1,0)))
Adjust and lock cell ranges to suit your data.
OPTIONAL DATE SELECTION
If you wind up having to select a column based on an actual date. you will need to know the the month and day of year that the fiscal year start or end on. Based on being before or after that date, you will convert the date to a string that matches the format of your column headers and look up that way.
Example
Fiscal Year Starts April 1st
Date you want to look up is 2017/10/23 (H1)
=IF(H1<Date(Year(H1),4,1),(YEAR(H1)-1)&"-"&RIGHT(YEAR(H1),2),YEAR(H1)&"-"&RIGHT(YEAR(H1)+1,2))
The above could be used in lieu of the data validation method described earlier and would be placed in G1.
answered Nov 23 '18 at 19:48
Forward Ed
6,51411336
edited Nov 23 '18 at 20:08
answered Nov 23 '18 at 19:48
Forward Ed
6,51411336
answered Nov 23 '18 at 19:48
Forward Ed
6,51411336
answered Nov 23 '18 at 19:48
Forward Ed
6,51411336
6,51411336
add a comment |
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2fstackoverflow.com%2fquestions%2f53451217%2ffind-max-fiscal-year-data-in-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
Are they dates that are formatted to appear like that (like actual dates in the cell) or are these strings that are in that format?
– JNevill
Nov 23 '18 at 18:09
No these are strings imputed in this format.
– User
Nov 23 '18 at 18:11
You could do
=DateValue(A1 & "-01")in a new column and then take the max of that. I think the important part is getting those converted to a real date since you can't take themax()of a string.– JNevill
Nov 23 '18 at 18:17
What does "-01" do?
– User
Nov 23 '18 at 18:22
That concatenates the
-01to the end of the date string like2017-08into2017-08-01(august 1st, 2018). TheDateValue()then converts that full date string into an actual excel date (stored as a 5 digit number). It's pretty common when just wanting to deal withYYYY-MMthat the actual date stored under the hood is the 1st of the month, so this fits that fairly common whole thing.– JNevill
Nov 23 '18 at 18:27