How can I use data in two columns, along with today’s date, to yield a new piece of information?
Microsoft Excel for Mac, Version 16.21.1
BACKGROUND and DATA:
I am a professor with a cumulative master roster of all the students I have taught. I have a column that tells me what year a particular student, e.g. “Bob,” (Row 3) took my class (Col. C = 2015). Another column lists Bob’s year in school (Col. D = third year) at the time he took my class.
PROCESS AND DESIRED OUTCOME:
I am hoping there is a way to create another column (Col. F) that will tell me what year in school a student is now/if a student is likely to still be on campus.
I am also hoping that by referring to a cell or column with the TODAY function, the new column will continually update. e.g. For a student who was a first year in Winter 2018, the new column would show the student to be a second year now. Next January, it would show the student to be a third year.
To do that, Excel will have to:
Take the information in Column C – the year Carly took my class – and:
a. Know that 15W = winter quarter of 2015 = January – March of each year. “15W” is my university’s format, so I need to keep it that way, but define the terms for Excel.
b. Know that JR = third year in school; SR = fourth year, etc. This is my university’s format, so I need to keep the “FR,” “JR,” etc., but define the terms for Excel.
c. Assume a four-year track for graduation. To make this easier, I want to assume the most common scenario. So Andy Andrews, who in 17S who was a FR (first year) began school in 16F (fall) and stayed a first year for 16W (winter) and 17S (spring), at which point he became a SO (second year) for 17F, 18W and 18S.
d. Compare the year Carly was in my class and her year in school when she took my class, with today’s date to give me an estimate of what year she is now or whether she graduated for my new Column F.
e. i.e. In Spring 2016 (Col. C), Carly was a first year (Col. D). Since it is now Winter 2019, Carly should be in her fourth year and graduating in 19S, Spring of this year.
# Do I need to create a column that will have the TODAY function in a
whole column of cells, all the way down? Just one cell
for the formula to refer to? Something else?
# It seems clunky to have an entire column of today’s date. Could the function refer to a single cell at the top of the sheet? Ora cell with hidden info?
- A complication: I need to tell excel that the terminal point for each year is after the Spring quarter, so that a student’s “year in school” changes after the S (Spring) part of a year. (We could call it July 1, to be on the safe side.) Same thing with graduation – it happens after S (or July 1) so that’s the point after which I need it my sheet to note that the student has graduated.
Thanks in advance for any thoughts!
Spreadsheet screenshot
microsoft-excel worksheet-function
asked Jan 31 at 10:10
MergieMergie
33
|
show 2 more comments
Microsoft Excel for Mac, Version 16.21.1
BACKGROUND and DATA:
I am a professor with a cumulative master roster of all the students I have taught. I have a column that tells me what year a particular student, e.g. “Bob,” (Row 3) took my class (Col. C = 2015). Another column lists Bob’s year in school (Col. D = third year) at the time he took my class.
PROCESS AND DESIRED OUTCOME:
I am hoping there is a way to create another column (Col. F) that will tell me what year in school a student is now/if a student is likely to still be on campus.
I am also hoping that by referring to a cell or column with the TODAY function, the new column will continually update. e.g. For a student who was a first year in Winter 2018, the new column would show the student to be a second year now. Next January, it would show the student to be a third year.
To do that, Excel will have to:
Take the information in Column C – the year Carly took my class – and:
a. Know that 15W = winter quarter of 2015 = January – March of each year. “15W” is my university’s format, so I need to keep it that way, but define the terms for Excel.
b. Know that JR = third year in school; SR = fourth year, etc. This is my university’s format, so I need to keep the “FR,” “JR,” etc., but define the terms for Excel.
c. Assume a four-year track for graduation. To make this easier, I want to assume the most common scenario. So Andy Andrews, who in 17S who was a FR (first year) began school in 16F (fall) and stayed a first year for 16W (winter) and 17S (spring), at which point he became a SO (second year) for 17F, 18W and 18S.
d. Compare the year Carly was in my class and her year in school when she took my class, with today’s date to give me an estimate of what year she is now or whether she graduated for my new Column F.
e. i.e. In Spring 2016 (Col. C), Carly was a first year (Col. D). Since it is now Winter 2019, Carly should be in her fourth year and graduating in 19S, Spring of this year.
# Do I need to create a column that will have the TODAY function in a
whole column of cells, all the way down? Just one cell
for the formula to refer to? Something else?
# It seems clunky to have an entire column of today’s date. Could the function refer to a single cell at the top of the sheet? Ora cell with hidden info?
- A complication: I need to tell excel that the terminal point for each year is after the Spring quarter, so that a student’s “year in school” changes after the S (Spring) part of a year. (We could call it July 1, to be on the safe side.) Same thing with graduation – it happens after S (or July 1) so that’s the point after which I need it my sheet to note that the student has graduated.
Thanks in advance for any thoughts!
Spreadsheet screenshot
microsoft-excel worksheet-function
asked Jan 31 at 10:10
MergieMergie
33
Does the student year designation always presume entry is from the beginning of the year, or could a student enter in winter and still be a FR the following spring?
– datatoo
Jan 31 at 21:28
In the first example the person took the class in 17S and was a FR could they have begun school in 16W
– datatoo
Jan 31 at 23:19
There are many, many possibilities including students who graduate in three years and students who graduate in five. But for my purposes, I'm fine with presuming the year changes happen in the fall and terminate after spring.
– Mergie
Feb 1 at 4:59
To further make clear, @datatoo, we're going to assume the most common scenario: The person in 17S who was a FR began school in 16F (fall) and stayed a FR for 16W and 17S at which point he/she became a SO.
– Mergie
Feb 1 at 5:01
@datatoo, I updated the main post to clarify. Thanks!
– Mergie
Feb 1 at 18:32
|
show 2 more comments
Microsoft Excel for Mac, Version 16.21.1
BACKGROUND and DATA:
I am a professor with a cumulative master roster of all the students I have taught. I have a column that tells me what year a particular student, e.g. “Bob,” (Row 3) took my class (Col. C = 2015). Another column lists Bob’s year in school (Col. D = third year) at the time he took my class.
PROCESS AND DESIRED OUTCOME:
I am hoping there is a way to create another column (Col. F) that will tell me what year in school a student is now/if a student is likely to still be on campus.
I am also hoping that by referring to a cell or column with the TODAY function, the new column will continually update. e.g. For a student who was a first year in Winter 2018, the new column would show the student to be a second year now. Next January, it would show the student to be a third year.
To do that, Excel will have to:
Take the information in Column C – the year Carly took my class – and:
a. Know that 15W = winter quarter of 2015 = January – March of each year. “15W” is my university’s format, so I need to keep it that way, but define the terms for Excel.
b. Know that JR = third year in school; SR = fourth year, etc. This is my university’s format, so I need to keep the “FR,” “JR,” etc., but define the terms for Excel.
c. Assume a four-year track for graduation. To make this easier, I want to assume the most common scenario. So Andy Andrews, who in 17S who was a FR (first year) began school in 16F (fall) and stayed a first year for 16W (winter) and 17S (spring), at which point he became a SO (second year) for 17F, 18W and 18S.
d. Compare the year Carly was in my class and her year in school when she took my class, with today’s date to give me an estimate of what year she is now or whether she graduated for my new Column F.
e. i.e. In Spring 2016 (Col. C), Carly was a first year (Col. D). Since it is now Winter 2019, Carly should be in her fourth year and graduating in 19S, Spring of this year.
# Do I need to create a column that will have the TODAY function in a
whole column of cells, all the way down? Just one cell
for the formula to refer to? Something else?
# It seems clunky to have an entire column of today’s date. Could the function refer to a single cell at the top of the sheet? Ora cell with hidden info?
- A complication: I need to tell excel that the terminal point for each year is after the Spring quarter, so that a student’s “year in school” changes after the S (Spring) part of a year. (We could call it July 1, to be on the safe side.) Same thing with graduation – it happens after S (or July 1) so that’s the point after which I need it my sheet to note that the student has graduated.
Thanks in advance for any thoughts!
Spreadsheet screenshot
microsoft-excel worksheet-function
asked Jan 31 at 10:10
MergieMergie
33
Microsoft Excel for Mac, Version 16.21.1
BACKGROUND and DATA:
I am a professor with a cumulative master roster of all the students I have taught. I have a column that tells me what year a particular student, e.g. “Bob,” (Row 3) took my class (Col. C = 2015). Another column lists Bob’s year in school (Col. D = third year) at the time he took my class.
PROCESS AND DESIRED OUTCOME:
I am hoping there is a way to create another column (Col. F) that will tell me what year in school a student is now/if a student is likely to still be on campus.
I am also hoping that by referring to a cell or column with the TODAY function, the new column will continually update. e.g. For a student who was a first year in Winter 2018, the new column would show the student to be a second year now. Next January, it would show the student to be a third year.
To do that, Excel will have to:
Take the information in Column C – the year Carly took my class – and:
a. Know that 15W = winter quarter of 2015 = January – March of each year. “15W” is my university’s format, so I need to keep it that way, but define the terms for Excel.
b. Know that JR = third year in school; SR = fourth year, etc. This is my university’s format, so I need to keep the “FR,” “JR,” etc., but define the terms for Excel.
c. Assume a four-year track for graduation. To make this easier, I want to assume the most common scenario. So Andy Andrews, who in 17S who was a FR (first year) began school in 16F (fall) and stayed a first year for 16W (winter) and 17S (spring), at which point he became a SO (second year) for 17F, 18W and 18S.
d. Compare the year Carly was in my class and her year in school when she took my class, with today’s date to give me an estimate of what year she is now or whether she graduated for my new Column F.
e. i.e. In Spring 2016 (Col. C), Carly was a first year (Col. D). Since it is now Winter 2019, Carly should be in her fourth year and graduating in 19S, Spring of this year.
# Do I need to create a column that will have the TODAY function in a
whole column of cells, all the way down? Just one cell
for the formula to refer to? Something else?
# It seems clunky to have an entire column of today’s date. Could the function refer to a single cell at the top of the sheet? Ora cell with hidden info?
- A complication: I need to tell excel that the terminal point for each year is after the Spring quarter, so that a student’s “year in school” changes after the S (Spring) part of a year. (We could call it July 1, to be on the safe side.) Same thing with graduation – it happens after S (or July 1) so that’s the point after which I need it my sheet to note that the student has graduated.
Thanks in advance for any thoughts!
Spreadsheet screenshot
microsoft-excel worksheet-function
microsoft-excel worksheet-function
asked Jan 31 at 10:10
MergieMergie
33
asked Jan 31 at 10:10
MergieMergie
33
edited Feb 1 at 18:31
Mergie
asked Jan 31 at 10:10
MergieMergie
33
asked Jan 31 at 10:10
MergieMergie
33
asked Jan 31 at 10:10
MergieMergie
33
33
Does the student year designation always presume entry is from the beginning of the year, or could a student enter in winter and still be a FR the following spring?
– datatoo
Jan 31 at 21:28
In the first example the person took the class in 17S and was a FR could they have begun school in 16W
– datatoo
Jan 31 at 23:19
There are many, many possibilities including students who graduate in three years and students who graduate in five. But for my purposes, I'm fine with presuming the year changes happen in the fall and terminate after spring.
– Mergie
Feb 1 at 4:59
To further make clear, @datatoo, we're going to assume the most common scenario: The person in 17S who was a FR began school in 16F (fall) and stayed a FR for 16W and 17S at which point he/she became a SO.
– Mergie
Feb 1 at 5:01
@datatoo, I updated the main post to clarify. Thanks!
– Mergie
Feb 1 at 18:32
|
show 2 more comments
Does the student year designation always presume entry is from the beginning of the year, or could a student enter in winter and still be a FR the following spring?
– datatoo
Jan 31 at 21:28
In the first example the person took the class in 17S and was a FR could they have begun school in 16W
– datatoo
Jan 31 at 23:19
There are many, many possibilities including students who graduate in three years and students who graduate in five. But for my purposes, I'm fine with presuming the year changes happen in the fall and terminate after spring.
– Mergie
Feb 1 at 4:59
To further make clear, @datatoo, we're going to assume the most common scenario: The person in 17S who was a FR began school in 16F (fall) and stayed a FR for 16W and 17S at which point he/she became a SO.
– Mergie
Feb 1 at 5:01
@datatoo, I updated the main post to clarify. Thanks!
– Mergie
Feb 1 at 18:32
Does the student year designation always presume entry is from the beginning of the year, or could a student enter in winter and still be a FR the following spring?
– datatoo
Jan 31 at 21:28
Does the student year designation always presume entry is from the beginning of the year, or could a student enter in winter and still be a FR the following spring?
– datatoo
Jan 31 at 21:28
In the first example the person took the class in 17S and was a FR could they have begun school in 16W
– datatoo
Jan 31 at 23:19
In the first example the person took the class in 17S and was a FR could they have begun school in 16W
– datatoo
Jan 31 at 23:19
There are many, many possibilities including students who graduate in three years and students who graduate in five. But for my purposes, I'm fine with presuming the year changes happen in the fall and terminate after spring.
– Mergie
Feb 1 at 4:59
There are many, many possibilities including students who graduate in three years and students who graduate in five. But for my purposes, I'm fine with presuming the year changes happen in the fall and terminate after spring.
– Mergie
Feb 1 at 4:59
To further make clear, @datatoo, we're going to assume the most common scenario: The person in 17S who was a FR began school in 16F (fall) and stayed a FR for 16W and 17S at which point he/she became a SO.
– Mergie
Feb 1 at 5:01
To further make clear, @datatoo, we're going to assume the most common scenario: The person in 17S who was a FR began school in 16F (fall) and stayed a FR for 16W and 17S at which point he/she became a SO.
– Mergie
Feb 1 at 5:01
@datatoo, I updated the main post to clarify. Thanks!
– Mergie
Feb 1 at 18:32
@datatoo, I updated the main post to clarify. Thanks!
– Mergie
Feb 1 at 18:32
|
show 2 more comments
0
active
oldest
votes
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%2f1400480%2fhow-can-i-use-data-in-two-columns-along-with-today-s-date-to-yield-a-new-piece%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f1400480%2fhow-can-i-use-data-in-two-columns-along-with-today-s-date-to-yield-a-new-piece%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
Does the student year designation always presume entry is from the beginning of the year, or could a student enter in winter and still be a FR the following spring?
– datatoo
Jan 31 at 21:28
In the first example the person took the class in 17S and was a FR could they have begun school in 16W
– datatoo
Jan 31 at 23:19
There are many, many possibilities including students who graduate in three years and students who graduate in five. But for my purposes, I'm fine with presuming the year changes happen in the fall and terminate after spring.
– Mergie
Feb 1 at 4:59
To further make clear, @datatoo, we're going to assume the most common scenario: The person in 17S who was a FR began school in 16F (fall) and stayed a FR for 16W and 17S at which point he/she became a SO.
– Mergie
Feb 1 at 5:01
@datatoo, I updated the main post to clarify. Thanks!
– Mergie
Feb 1 at 18:32