In Excel - how to compare a date to today's date, but ignoring the year portion?
I have a large and constantly evolving list of contact's birthdays. They all include the year, but that is irrelevant to our needs.
I would like to have a cell that calculates whether a birthday (day and month) is between Today and 14 days from today.
I have been using this formula:
=IF(F5>TODAY()+14,"later", "earlier")
But of course, it is comparing everything including the year - so of course everyone that was born before today. I know my formula isn't complete, but I'm just using that for the purposes of clarity.
microsoft-excel date
asked Jan 7 at 23:03
BlobbyBlobby
32
add a comment |
I have a large and constantly evolving list of contact's birthdays. They all include the year, but that is irrelevant to our needs.
I would like to have a cell that calculates whether a birthday (day and month) is between Today and 14 days from today.
I have been using this formula:
=IF(F5>TODAY()+14,"later", "earlier")
But of course, it is comparing everything including the year - so of course everyone that was born before today. I know my formula isn't complete, but I'm just using that for the purposes of clarity.
microsoft-excel date
asked Jan 7 at 23:03
BlobbyBlobby
32
You should use the appropriate button to indicate that one of these answers works (or works better) for you. This way future visitors will see which one answered your question, and the people who gave the answer will receive appropriate points.
– music2myear
Jan 7 at 23:52
add a comment |
I have a large and constantly evolving list of contact's birthdays. They all include the year, but that is irrelevant to our needs.
I would like to have a cell that calculates whether a birthday (day and month) is between Today and 14 days from today.
I have been using this formula:
=IF(F5>TODAY()+14,"later", "earlier")
But of course, it is comparing everything including the year - so of course everyone that was born before today. I know my formula isn't complete, but I'm just using that for the purposes of clarity.
microsoft-excel date
asked Jan 7 at 23:03
BlobbyBlobby
32
I have a large and constantly evolving list of contact's birthdays. They all include the year, but that is irrelevant to our needs.
I would like to have a cell that calculates whether a birthday (day and month) is between Today and 14 days from today.
I have been using this formula:
=IF(F5>TODAY()+14,"later", "earlier")
But of course, it is comparing everything including the year - so of course everyone that was born before today. I know my formula isn't complete, but I'm just using that for the purposes of clarity.
microsoft-excel date
microsoft-excel date
asked Jan 7 at 23:03
BlobbyBlobby
32
asked Jan 7 at 23:03
BlobbyBlobby
32
asked Jan 7 at 23:03
BlobbyBlobby
32
asked Jan 7 at 23:03
BlobbyBlobby
32
asked Jan 7 at 23:03
BlobbyBlobby
32
32
You should use the appropriate button to indicate that one of these answers works (or works better) for you. This way future visitors will see which one answered your question, and the people who gave the answer will receive appropriate points.
– music2myear
Jan 7 at 23:52
add a comment |
You should use the appropriate button to indicate that one of these answers works (or works better) for you. This way future visitors will see which one answered your question, and the people who gave the answer will receive appropriate points.
– music2myear
Jan 7 at 23:52
You should use the appropriate button to indicate that one of these answers works (or works better) for you. This way future visitors will see which one answered your question, and the people who gave the answer will receive appropriate points.
– music2myear
Jan 7 at 23:52
You should use the appropriate button to indicate that one of these answers works (or works better) for you. This way future visitors will see which one answered your question, and the people who gave the answer will receive appropriate points.
– music2myear
Jan 7 at 23:52
add a comment |
2 Answers
2
active
oldest
votes
Use DATE(YEAR(TODAY()),MONTH(F5),DAY(F5)) to return a date that is this year on that day and month.
=IF(DATE(YEAR(TODAY()),MONTH(F5),DAY(F5))>TODAY() + 14,"later",IF(DATE(YEAR(TODAY()),MONTH(F5),DAY(F5))<TODAY(),"Past",IF(DATE(YEAR(TODAY()),MONTH(F5),DAY(F5))=TODAY(),"Today","With in Two Weeks")))
answered Jan 7 at 23:07
Scott CranerScott Craner
11.7k1815
Thanks, I like this way too. Edit: came back to say that this probably works better for me - as it includes the entire calculation for the "within two weeks". Thanks!
– Blobby
Jan 7 at 23:38
add a comment |
Here is one way:
=IF(TEXT(F5,"dd/mm")>TEXT(TODAY()+14,"dd/mm"),"later","earlier")
answered Jan 7 at 23:15
BrianBrian
3475
Terrific! That seems to work :)
– Blobby
Jan 7 at 23:37
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%2f1391702%2fin-excel-how-to-compare-a-date-to-todays-date-but-ignoring-the-year-portion%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
Use DATE(YEAR(TODAY()),MONTH(F5),DAY(F5)) to return a date that is this year on that day and month.
=IF(DATE(YEAR(TODAY()),MONTH(F5),DAY(F5))>TODAY() + 14,"later",IF(DATE(YEAR(TODAY()),MONTH(F5),DAY(F5))<TODAY(),"Past",IF(DATE(YEAR(TODAY()),MONTH(F5),DAY(F5))=TODAY(),"Today","With in Two Weeks")))
answered Jan 7 at 23:07
Scott CranerScott Craner
11.7k1815
Thanks, I like this way too. Edit: came back to say that this probably works better for me - as it includes the entire calculation for the "within two weeks". Thanks!
– Blobby
Jan 7 at 23:38
add a comment |
Use DATE(YEAR(TODAY()),MONTH(F5),DAY(F5)) to return a date that is this year on that day and month.
=IF(DATE(YEAR(TODAY()),MONTH(F5),DAY(F5))>TODAY() + 14,"later",IF(DATE(YEAR(TODAY()),MONTH(F5),DAY(F5))<TODAY(),"Past",IF(DATE(YEAR(TODAY()),MONTH(F5),DAY(F5))=TODAY(),"Today","With in Two Weeks")))
answered Jan 7 at 23:07
Scott CranerScott Craner
11.7k1815
Thanks, I like this way too. Edit: came back to say that this probably works better for me - as it includes the entire calculation for the "within two weeks". Thanks!
– Blobby
Jan 7 at 23:38
add a comment |
Use DATE(YEAR(TODAY()),MONTH(F5),DAY(F5)) to return a date that is this year on that day and month.
=IF(DATE(YEAR(TODAY()),MONTH(F5),DAY(F5))>TODAY() + 14,"later",IF(DATE(YEAR(TODAY()),MONTH(F5),DAY(F5))<TODAY(),"Past",IF(DATE(YEAR(TODAY()),MONTH(F5),DAY(F5))=TODAY(),"Today","With in Two Weeks")))
answered Jan 7 at 23:07
Scott CranerScott Craner
11.7k1815
Use DATE(YEAR(TODAY()),MONTH(F5),DAY(F5)) to return a date that is this year on that day and month.
=IF(DATE(YEAR(TODAY()),MONTH(F5),DAY(F5))>TODAY() + 14,"later",IF(DATE(YEAR(TODAY()),MONTH(F5),DAY(F5))<TODAY(),"Past",IF(DATE(YEAR(TODAY()),MONTH(F5),DAY(F5))=TODAY(),"Today","With in Two Weeks")))
answered Jan 7 at 23:07
Scott CranerScott Craner
11.7k1815
answered Jan 7 at 23:07
Scott CranerScott Craner
11.7k1815
answered Jan 7 at 23:07
Scott CranerScott Craner
11.7k1815
answered Jan 7 at 23:07
Scott CranerScott Craner
11.7k1815
11.7k1815
Thanks, I like this way too. Edit: came back to say that this probably works better for me - as it includes the entire calculation for the "within two weeks". Thanks!
– Blobby
Jan 7 at 23:38
add a comment |
Thanks, I like this way too. Edit: came back to say that this probably works better for me - as it includes the entire calculation for the "within two weeks". Thanks!
– Blobby
Jan 7 at 23:38
Thanks, I like this way too. Edit: came back to say that this probably works better for me - as it includes the entire calculation for the "within two weeks". Thanks!
– Blobby
Jan 7 at 23:38
Thanks, I like this way too. Edit: came back to say that this probably works better for me - as it includes the entire calculation for the "within two weeks". Thanks!
– Blobby
Jan 7 at 23:38
add a comment |
Here is one way:
=IF(TEXT(F5,"dd/mm")>TEXT(TODAY()+14,"dd/mm"),"later","earlier")
answered Jan 7 at 23:15
BrianBrian
3475
Terrific! That seems to work :)
– Blobby
Jan 7 at 23:37
add a comment |
Here is one way:
=IF(TEXT(F5,"dd/mm")>TEXT(TODAY()+14,"dd/mm"),"later","earlier")
answered Jan 7 at 23:15
BrianBrian
3475
Terrific! That seems to work :)
– Blobby
Jan 7 at 23:37
add a comment |
Here is one way:
=IF(TEXT(F5,"dd/mm")>TEXT(TODAY()+14,"dd/mm"),"later","earlier")
answered Jan 7 at 23:15
BrianBrian
3475
Here is one way:
=IF(TEXT(F5,"dd/mm")>TEXT(TODAY()+14,"dd/mm"),"later","earlier")
answered Jan 7 at 23:15
BrianBrian
3475
answered Jan 7 at 23:15
BrianBrian
3475
answered Jan 7 at 23:15
BrianBrian
3475
answered Jan 7 at 23:15
BrianBrian
3475
3475
Terrific! That seems to work :)
– Blobby
Jan 7 at 23:37
add a comment |
Terrific! That seems to work :)
– Blobby
Jan 7 at 23:37
Terrific! That seems to work :)
– Blobby
Jan 7 at 23:37
Terrific! That seems to work :)
– Blobby
Jan 7 at 23:37
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%2f1391702%2fin-excel-how-to-compare-a-date-to-todays-date-but-ignoring-the-year-portion%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
You should use the appropriate button to indicate that one of these answers works (or works better) for you. This way future visitors will see which one answered your question, and the people who gave the answer will receive appropriate points.
– music2myear
Jan 7 at 23:52