+ Start a Discussion
Julia ZocoloJulia Zocolo 

using month and date only in formula

Hi Team,

I built this formula a few years ago and it's time to update it.
I was not able to make it worth just using the MM and DD values, which would be sustainable and shorter.

It's basically checking if the release date (Invoice sent date) is in between our specific payout schedule. 

Pay out Calendar

Any advice is greatly appreciated!

Formula field currently:

IF( cld__ReleaseDate__c < DATE(2019,01,01), cld__ReleaseDate__c ,
IF( cld__Date2__c < DATE(2019,01,01) && cld__ReleaseDate__c >= DATE(2019,01,01), cld__ReleaseDate__c ,
IF( cld__ReleaseDate__c >= DATE(2019,01,01) && cld__ReleaseDate__c <= DATE(2019,04,10), DATE(2019,04,01),
IF( cld__ReleaseDate__c >= DATE(2019,04,11) && cld__ReleaseDate__c <= DATE(2019,07,10), DATE(2019,07,01),
IF( cld__ReleaseDate__c >= DATE(2019,07,11) && cld__ReleaseDate__c <= DATE(2019,10,10), DATE(2019,10,01),
IF( cld__ReleaseDate__c >= DATE(2019,10,11) && cld__ReleaseDate__c <= DATE(2020,01,10), DATE(2020,01,01),
IF( cld__ReleaseDate__c >= DATE(2020,01,11) && cld__ReleaseDate__c <= DATE(2020,04,10), DATE(2020,04,01),
IF( cld__ReleaseDate__c >= DATE(2020,04,11) && cld__ReleaseDate__c <= DATE(2020,07,10), DATE(2020,07,01),
IF( cld__ReleaseDate__c >= DATE(2020,07,11) && cld__ReleaseDate__c <= DATE(2020,10,10), DATE(2020,10,01),
IF( cld__ReleaseDate__c >= DATE(2020,10,11) && cld__ReleaseDate__c <= DATE(2021,01,10), DATE(2021,01,01),
IF( cld__ReleaseDate__c >= DATE(2021,01,11) && cld__ReleaseDate__c <= DATE(2021,04,10), DATE(2021,04,01),
IF( cld__ReleaseDate__c >= DATE(2021,04,11) && cld__ReleaseDate__c <= DATE(2021,07,10), DATE(2021,07,01),
IF( cld__ReleaseDate__c >= DATE(2021,07,11) && cld__ReleaseDate__c <= DATE(2021,10,10), DATE(2021,10,01),
IF( cld__ReleaseDate__c >= DATE(2021,10,11) && cld__ReleaseDate__c <= DATE(2022,01,10), DATE(2022,01,01),
null
))))))))))))))


 
CharuDuttCharuDutt
Hii Julia
Try Below Code
IF( cld__ReleaseDate__c < DATE(YEAR(TODAY()),01,01), cld__ReleaseDate__c ,
IF( cld__Date2__c < DATE(YEAR(TODAY()),01,01) && cld__ReleaseDate__c >= DATE(YEAR(TODAY()),01,01), cld__ReleaseDate__c ,
IF( cld__ReleaseDate__c >= DATE(YEAR(TODAY()),01,01) && cld__ReleaseDate__c <= DATE(YEAR(TODAY()),04,10), DATE(YEAR(TODAY()),04,01),
IF( cld__ReleaseDate__c >= DATE(YEAR(TODAY()),04,11) && cld__ReleaseDate__c <= DATE(YEAR(TODAY()),07,10), DATE(YEAR(TODAY()),07,01),
IF( cld__ReleaseDate__c >= DATE(YEAR(TODAY()),07,11) && cld__ReleaseDate__c <= DATE(YEAR(TODAY()),10,10), DATE(YEAR(TODAY()),10,01))))))
Please Mark It As Best Answer If It Helps
Thank You!
Alain CabonAlain Cabon
It is difficult (impossible) to go any further than the CharuDutt's formula with the calculation of the quarter (month) because it is not a number of months but of days (100, 91, 90 ...) beginning at 1st or 11th.