Apex Code Development You need to sign in to do that
Don't have an account?
Formula to compare if Month/Day only are in between a range of month/day only
Hello,
I am trying to write several formulas to use in a flow to check if a date is in between a range. The issue is I want it to be sustainable and only compare the Month and Day only, not the year.
Example:
say the input date field is March 1, 2023
I want to know if it's equal to or greater than January 11 and less than or equal to April 10th and not include the year.
Is this possible?
Tried something like this and it failed:
AND(
MONTH({!$Record.Comp_Approved_Date__c}) >= 1,
MONTH({!$Record.Comp_Approved_Date__c}) <= 4,
DAY({!$Record.Comp_Approved_Date__c}) >= 11,
DAY({!$Record.Comp_Approved_Date__c}) <= 10
)
I am trying to write several formulas to use in a flow to check if a date is in between a range. The issue is I want it to be sustainable and only compare the Month and Day only, not the year.
Example:
say the input date field is March 1, 2023
I want to know if it's equal to or greater than January 11 and less than or equal to April 10th and not include the year.
Is this possible?
Tried something like this and it failed:
AND(
MONTH({!$Record.Comp_Approved_Date__c}) >= 1,
MONTH({!$Record.Comp_Approved_Date__c}) <= 4,
DAY({!$Record.Comp_Approved_Date__c}) >= 11,
DAY({!$Record.Comp_Approved_Date__c}) <= 10
)
Thank you for your response. We ran into an issue with it breaking for several end dates like March 31st since it can't pass BOTH of the last conditions of that AND funciton.
We have solved this query like this:
=IF(
AND(MONTH({!$Record.Comp_Approved_Date__c}) = 1, DAY({!$Record.Comp_Approved_Date__c}) >=11), TRUE,
IF (AND(MONTH({!$Record.Comp_Approved_Date__c}) =4, DAY({!$Record.Comp_Approved_Date__c}) <=10), TRUE,
IF(AND(MONTH({!$Record.Comp_Approved_Date__c}) > 1, MONTH({!$Record.Comp_Approved_Date__c}) <4), TRUE,
FALSE))
I appreciate your help and support!
All Answers
Here is an example formula that checks if a date is between January 11 and April 10, regardless of the year:
AND( MONTH({!$Record.Comp_Approved_Date__c}) >= MONTH(DATE(2000, 1, 11)), MONTH({!$Record.Comp_Approved_Date__c}) <= MONTH(DATE(2000, 4, 10)), DAY({!$Record.Comp_Approved_Date__c}) >= DAY(DATE(2000, 1, 11)), DAY({!$Record.Comp_Approved_Date__c}) <= DAY(DATE(2000, 4, 10)) )In this formula, the DATE() function is used to create a new date with the year set to 2000 (you can use any year as long as it is a leap year). Then, the MONTH() and DAY() functions are used to extract the month and day values from the input date and the comparison dates.
Note that if you want to include April 10th as well, you need to use the less than or equal to operator (<=) instead of just the less than operator (<) in the last comparison.
You can modify this formula as needed to match your specific requirements.
Thank you for your response. We ran into an issue with it breaking for several end dates like March 31st since it can't pass BOTH of the last conditions of that AND funciton.
We have solved this query like this:
=IF(
AND(MONTH({!$Record.Comp_Approved_Date__c}) = 1, DAY({!$Record.Comp_Approved_Date__c}) >=11), TRUE,
IF (AND(MONTH({!$Record.Comp_Approved_Date__c}) =4, DAY({!$Record.Comp_Approved_Date__c}) <=10), TRUE,
IF(AND(MONTH({!$Record.Comp_Approved_Date__c}) > 1, MONTH({!$Record.Comp_Approved_Date__c}) <4), TRUE,
FALSE))
I appreciate your help and support!