Trying to create a formula to show yes/no if the 12th of the month falls between {"Payroll Period"."Start Date"} and {"Payroll Period"."End Date"} I attempted a simple formula: CASE WHEN DAY("Payroll Period"."Start Date") =12 THEN 'Yes' WHEN DAY("Payroll Period"."End Date") = 12 THEN 'Yes' END But pay periods - 12/31/2023 - 01/13/2024 and 06/30/2024 - 07/13/2024 are being excluded from that logic. Thank you, Andrew

Oracle Communities

Oracle Analytics and AI

Child Item

How to find if a specific day falls between two dates - OTBI

Trying to create a formula to show yes/no if the 12th of the month falls between {"Payroll Period"."Start Date"} and {"Payroll Period"."End Date"}

I attempted a simple formula:

CASE WHEN DAY("Payroll Period"."Start Date") <= 12 and DAY("Payroll Period"."End Date") >=12 THEN 'Yes' WHEN DAY("Payroll Period"."End Date") = 12 THEN 'Yes' END

But pay periods - 12/31/2023 - 01/13/2024 and 06/30/2024 - 07/13/2024 are being excluded from that logic.

Thank you,

Andrew

Find more posts tagged with

OTBI

Accepted answers

Nathan CCC

So for a day of the month n=12 to be in a date range from start date YYYY-mm-dd to end date YYYY-mm-dd then assuming end date > = start date then i think you need to test how many months between the start end dates. To include day 12; If same year month difference in months = 0 then start date must be > = 12 AND end date must be > = 12 . If consecutive months difference in months = 1 then start date must be > = 12 OR end date must be < = 12. If difference in months = 2+ then the data range will always include day 1-28 including day 12.

All comments

Nathan CCC

Hi,

For test case 1 payroll day of start date 2023-12-31 = 31 and day of end date 2024-01-13 is 13. 31 is NOT "< = 12", 13 is NOT "> = 12", 13 is not "= 12" - so it is not true for any of your 3 conditions in your switch so it is nether Yes nor Yes - the case will return null.

For test case 2 payroll day of start date 2024-06-30 = 30 and day of end date 2024-07-13 is 13. 30 is NOT "lt = 12", 13 is NOT "gt = 12", 13 is not "= 12" - so it is not true for any of your 3 conditions in your switch so it is nether Yes nor Yes - the case will return null.

Nathan CCC

Andrew Mc

Thank you so much Nathan, that looks like it worked for me:

CASE WHEN ((YEAR("Payroll Period"."End Date") - YEAR("Payroll Period"."Start Date")) * 12 + (MONTH("Payroll Period"."End Date") - MONTH("Payroll Period"."Start Date")) = 0 ) AND (DAY("Payroll Period"."Start Date") <= 12 AND DAY("Payroll Period"."End Date") >= 12) THEN 'Yes' WHEN ((YEAR("Payroll Period"."End Date") - YEAR("Payroll Period"."Start Date")) * 12 + (MONTH("Payroll Period"."End Date") - MONTH("Payroll Period"."Start Date")) = 1 ) AND (DAY("Payroll Period"."Start Date") >= 12 AND DAY("Payroll Period"."End Date") >= 12) THEN 'Yes' END

I checked back a couple of years and it appears to be working and it shows as 'Yes' with dates that have the 12th day in the range.

Appreciate the help.