You need to sign in to do that
Don't have an account?
Julia 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.
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
))))))))))))))
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.
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
))))))))))))))
Try Below Code Please Mark It As Best Answer If It Helps
Thank You!