Formula based around dates
I am attempting to write a formula that will indicate when coverage of a specific product will go into effect based on the purchase date. Per the rules in this line of business, if a customer purchases a product after the 1st of any given month, the paid coverage of the product defaults to the 1st of the following month.
E.g. Customer Agreement starts 2/4/2019, so coverage would default to 3/1/2019; however, if the Agreement starts 2/1/2019, coverage would default to the same date (2/1/2019)
Is it possible to write a formula that will suit my needs where any order placed on the 1st of the month would start coverage same day, while all other dates would default to the 1st of the following month?
I have a formula along similar lines that but it serves a different purpose:
K185 = the Agreement Start Date
I90 = Invoice Date
=IF(OR(I90='2b - LookUp Values-UI'!K185,I90=TODAY()),"PASS","FAIL")
Thanks in advance for any assistance provided!
microsoft-excel worksheet-function
asked Feb 12 at 0:15
ArgentKingArgentKing
316
add a comment |
I am attempting to write a formula that will indicate when coverage of a specific product will go into effect based on the purchase date. Per the rules in this line of business, if a customer purchases a product after the 1st of any given month, the paid coverage of the product defaults to the 1st of the following month.
E.g. Customer Agreement starts 2/4/2019, so coverage would default to 3/1/2019; however, if the Agreement starts 2/1/2019, coverage would default to the same date (2/1/2019)
Is it possible to write a formula that will suit my needs where any order placed on the 1st of the month would start coverage same day, while all other dates would default to the 1st of the following month?
I have a formula along similar lines that but it serves a different purpose:
K185 = the Agreement Start Date
I90 = Invoice Date
=IF(OR(I90='2b - LookUp Values-UI'!K185,I90=TODAY()),"PASS","FAIL")
Thanks in advance for any assistance provided!
microsoft-excel worksheet-function
asked Feb 12 at 0:15
ArgentKingArgentKing
316
add a comment |
I am attempting to write a formula that will indicate when coverage of a specific product will go into effect based on the purchase date. Per the rules in this line of business, if a customer purchases a product after the 1st of any given month, the paid coverage of the product defaults to the 1st of the following month.
E.g. Customer Agreement starts 2/4/2019, so coverage would default to 3/1/2019; however, if the Agreement starts 2/1/2019, coverage would default to the same date (2/1/2019)
Is it possible to write a formula that will suit my needs where any order placed on the 1st of the month would start coverage same day, while all other dates would default to the 1st of the following month?
I have a formula along similar lines that but it serves a different purpose:
K185 = the Agreement Start Date
I90 = Invoice Date
=IF(OR(I90='2b - LookUp Values-UI'!K185,I90=TODAY()),"PASS","FAIL")
Thanks in advance for any assistance provided!
microsoft-excel worksheet-function
asked Feb 12 at 0:15
ArgentKingArgentKing
316
I am attempting to write a formula that will indicate when coverage of a specific product will go into effect based on the purchase date. Per the rules in this line of business, if a customer purchases a product after the 1st of any given month, the paid coverage of the product defaults to the 1st of the following month.
E.g. Customer Agreement starts 2/4/2019, so coverage would default to 3/1/2019; however, if the Agreement starts 2/1/2019, coverage would default to the same date (2/1/2019)
Is it possible to write a formula that will suit my needs where any order placed on the 1st of the month would start coverage same day, while all other dates would default to the 1st of the following month?
I have a formula along similar lines that but it serves a different purpose:
K185 = the Agreement Start Date
I90 = Invoice Date
=IF(OR(I90='2b - LookUp Values-UI'!K185,I90=TODAY()),"PASS","FAIL")
Thanks in advance for any assistance provided!
microsoft-excel worksheet-function
microsoft-excel worksheet-function
asked Feb 12 at 0:15
ArgentKingArgentKing
316
asked Feb 12 at 0:15
ArgentKingArgentKing
316
asked Feb 12 at 0:15
ArgentKingArgentKing
316
asked Feb 12 at 0:15
ArgentKingArgentKing
316
asked Feb 12 at 0:15
ArgentKingArgentKing
316
316
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Use DAY() if the day of the month in A1 is not 1 then it will add on month and return the 1st of the following month.
=IF(DAY(A1)=1,A1,DATE(YEAR(A1),MONTH(A1)+1,1))
answered Feb 12 at 0:21
Scott CranerScott Craner
12.5k11318
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%2f1404641%2fformula-based-around-dates%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
Use DAY() if the day of the month in A1 is not 1 then it will add on month and return the 1st of the following month.
=IF(DAY(A1)=1,A1,DATE(YEAR(A1),MONTH(A1)+1,1))
answered Feb 12 at 0:21
Scott CranerScott Craner
12.5k11318
add a comment |
Use DAY() if the day of the month in A1 is not 1 then it will add on month and return the 1st of the following month.
=IF(DAY(A1)=1,A1,DATE(YEAR(A1),MONTH(A1)+1,1))
answered Feb 12 at 0:21
Scott CranerScott Craner
12.5k11318
add a comment |
Use DAY() if the day of the month in A1 is not 1 then it will add on month and return the 1st of the following month.
=IF(DAY(A1)=1,A1,DATE(YEAR(A1),MONTH(A1)+1,1))
answered Feb 12 at 0:21
Scott CranerScott Craner
12.5k11318
Use DAY() if the day of the month in A1 is not 1 then it will add on month and return the 1st of the following month.
=IF(DAY(A1)=1,A1,DATE(YEAR(A1),MONTH(A1)+1,1))
answered Feb 12 at 0:21
Scott CranerScott Craner
12.5k11318
answered Feb 12 at 0:21
Scott CranerScott Craner
12.5k11318
answered Feb 12 at 0:21
Scott CranerScott Craner
12.5k11318
answered Feb 12 at 0:21
Scott CranerScott Craner
12.5k11318
12.5k11318
add a comment |
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%2f1404641%2fformula-based-around-dates%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
