Excel - Date detection/comparison in mixed format cells
TL;DR - i need to count cells in a row that include dates between 10/1/2018 - 9/30/2019 OR "Not Required", this is the part that's stumping me.
I'm using excel 2016 to create a training tracker for my workplace. It will be used to track the completion dates of various training programs we use for my department, so each member will have their own row. The values in each cells are actually pulled from another department and are mixed format, so i need to be able to parse the data for different values. The cells I need help with will calculate running totals. at the end of each row, i need to calculate each member's overall completion (member completed x amount divided by y total trainings), and at the bottom of each column i need to calculate how many members have completed the specific training (x number completed divided by y number of total members). Completion of training shall be marked by the completion date (formatted mm/dd/yyyy). I need to only include cells with those dates that fall within this fiscal year (10/1/2018 - 9/30/2019). Some cells will also be marked "Not Required" and also need to be included in the calculated completion rates.
microsoft-excel worksheet-function microsoft-excel-2016
asked Jan 28 at 17:00
Ein McDrummiesEin McDrummies
41
add a comment |
TL;DR - i need to count cells in a row that include dates between 10/1/2018 - 9/30/2019 OR "Not Required", this is the part that's stumping me.
I'm using excel 2016 to create a training tracker for my workplace. It will be used to track the completion dates of various training programs we use for my department, so each member will have their own row. The values in each cells are actually pulled from another department and are mixed format, so i need to be able to parse the data for different values. The cells I need help with will calculate running totals. at the end of each row, i need to calculate each member's overall completion (member completed x amount divided by y total trainings), and at the bottom of each column i need to calculate how many members have completed the specific training (x number completed divided by y number of total members). Completion of training shall be marked by the completion date (formatted mm/dd/yyyy). I need to only include cells with those dates that fall within this fiscal year (10/1/2018 - 9/30/2019). Some cells will also be marked "Not Required" and also need to be included in the calculated completion rates.
microsoft-excel worksheet-function microsoft-excel-2016
asked Jan 28 at 17:00
Ein McDrummiesEin McDrummies
41
IF(A1="Not Required",*result if not required*,*result if date*)? This would be easier to answer if we had sample data, expected result, what you've tried so far and where the problem is, exactly
– cybernetic.nomad
Jan 28 at 18:47
Could you provide a sample about your problem?
– Lee
Jan 29 at 8:57
Have a try into using date(), today(), day() month() & year() function.. || Share where your get stuck with your code and we may proceed from there.. ( :
– p._phidot_
Jan 31 at 2:02
add a comment |
TL;DR - i need to count cells in a row that include dates between 10/1/2018 - 9/30/2019 OR "Not Required", this is the part that's stumping me.
I'm using excel 2016 to create a training tracker for my workplace. It will be used to track the completion dates of various training programs we use for my department, so each member will have their own row. The values in each cells are actually pulled from another department and are mixed format, so i need to be able to parse the data for different values. The cells I need help with will calculate running totals. at the end of each row, i need to calculate each member's overall completion (member completed x amount divided by y total trainings), and at the bottom of each column i need to calculate how many members have completed the specific training (x number completed divided by y number of total members). Completion of training shall be marked by the completion date (formatted mm/dd/yyyy). I need to only include cells with those dates that fall within this fiscal year (10/1/2018 - 9/30/2019). Some cells will also be marked "Not Required" and also need to be included in the calculated completion rates.
microsoft-excel worksheet-function microsoft-excel-2016
asked Jan 28 at 17:00
Ein McDrummiesEin McDrummies
41
TL;DR - i need to count cells in a row that include dates between 10/1/2018 - 9/30/2019 OR "Not Required", this is the part that's stumping me.
I'm using excel 2016 to create a training tracker for my workplace. It will be used to track the completion dates of various training programs we use for my department, so each member will have their own row. The values in each cells are actually pulled from another department and are mixed format, so i need to be able to parse the data for different values. The cells I need help with will calculate running totals. at the end of each row, i need to calculate each member's overall completion (member completed x amount divided by y total trainings), and at the bottom of each column i need to calculate how many members have completed the specific training (x number completed divided by y number of total members). Completion of training shall be marked by the completion date (formatted mm/dd/yyyy). I need to only include cells with those dates that fall within this fiscal year (10/1/2018 - 9/30/2019). Some cells will also be marked "Not Required" and also need to be included in the calculated completion rates.
microsoft-excel worksheet-function microsoft-excel-2016
microsoft-excel worksheet-function microsoft-excel-2016
asked Jan 28 at 17:00
Ein McDrummiesEin McDrummies
41
asked Jan 28 at 17:00
Ein McDrummiesEin McDrummies
41
asked Jan 28 at 17:00
Ein McDrummiesEin McDrummies
41
asked Jan 28 at 17:00
Ein McDrummiesEin McDrummies
41
asked Jan 28 at 17:00
Ein McDrummiesEin McDrummies
41
41
IF(A1="Not Required",*result if not required*,*result if date*)? This would be easier to answer if we had sample data, expected result, what you've tried so far and where the problem is, exactly
– cybernetic.nomad
Jan 28 at 18:47
Could you provide a sample about your problem?
– Lee
Jan 29 at 8:57
Have a try into using date(), today(), day() month() & year() function.. || Share where your get stuck with your code and we may proceed from there.. ( :
– p._phidot_
Jan 31 at 2:02
add a comment |
IF(A1="Not Required",*result if not required*,*result if date*)? This would be easier to answer if we had sample data, expected result, what you've tried so far and where the problem is, exactly
– cybernetic.nomad
Jan 28 at 18:47
Could you provide a sample about your problem?
– Lee
Jan 29 at 8:57
Have a try into using date(), today(), day() month() & year() function.. || Share where your get stuck with your code and we may proceed from there.. ( :
– p._phidot_
Jan 31 at 2:02
IF(A1="Not Required",*result if not required*,*result if date*)? This would be easier to answer if we had sample data, expected result, what you've tried so far and where the problem is, exactly– cybernetic.nomad
Jan 28 at 18:47
IF(A1="Not Required",*result if not required*,*result if date*)? This would be easier to answer if we had sample data, expected result, what you've tried so far and where the problem is, exactly– cybernetic.nomad
Jan 28 at 18:47
Could you provide a sample about your problem?
– Lee
Jan 29 at 8:57
Could you provide a sample about your problem?
– Lee
Jan 29 at 8:57
Have a try into using date(), today(), day() month() & year() function.. || Share where your get stuck with your code and we may proceed from there.. ( :
– p._phidot_
Jan 31 at 2:02
Have a try into using date(), today(), day() month() & year() function.. || Share where your get stuck with your code and we may proceed from there.. ( :
– p._phidot_
Jan 31 at 2:02
add a comment |
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%2f1399341%2fexcel-date-detection-comparison-in-mixed-format-cells%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%2f1399341%2fexcel-date-detection-comparison-in-mixed-format-cells%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(A1="Not Required",*result if not required*,*result if date*)? This would be easier to answer if we had sample data, expected result, what you've tried so far and where the problem is, exactly– cybernetic.nomad
Jan 28 at 18:47
Could you provide a sample about your problem?
– Lee
Jan 29 at 8:57
Have a try into using date(), today(), day() month() & year() function.. || Share where your get stuck with your code and we may proceed from there.. ( :
– p._phidot_
Jan 31 at 2:02