function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Stephen02Stephen02 

Efficient Formula for Calculating Date

Trying to determine a more efficient way to write the below formula.  Basically it evaluates the current day of the week, and if Monday - Thursday (e.g. 1-4)  and it's before 1PM then return Today, else return Today + 1. IF the day is Friday before 1PM then return Today, else Today + 3. If Saturday (6), return Today + 2. If Sunday (7), return today +2

IF(
                OR(CM_Test_Date_Field__c = "1",CM_Test_Date_Field__c = "2",CM_Test_Date_Field__c = "3",CM_Test_Date_Field__c = "4",CM_Test_Date_Field__c = "7"),
                IF(NOW() < DATETIMEVALUE(TODAY())+(19/24), TEXT(YEAR(TODAY())) + LPAD(TEXT(MONTH(TODAY())),2,"0") + LPAD(TEXT(DAY(TODAY())),2,"0"), TEXT(YEAR(TODAY()+1)) + LPAD(TEXT(MONTH(TODAY()+1)),2,"0") + LPAD(TEXT(DAY(TODAY()+1)),2,"0")),
                IF(CM_Test_Date_Field__c = "5",IF(NOW() < DATETIMEVALUE(TODAY())+(19/24), TEXT(YEAR(TODAY())) + LPAD(TEXT(MONTH(TODAY())),2,"0") + LPAD(TEXT(DAY(TODAY())),2,"0"), TEXT(YEAR(TODAY()+3)) + LPAD(TEXT(MONTH(TODAY()+3)),2,"0") + LPAD(TEXT(DAY(TODAY()+3)),2,"0")),TEXT(YEAR(TODAY()+2)) + LPAD(TEXT(MONTH(TODAY()+2)),2,"0") + LPAD(TEXT(DAY(TODAY()+2)),2,"0")))
 
MellowRenMellowRen
Trying to think of a clever mathmatical based solution but not having a lot of success. My initial thought is to make a new field—one for the “adjustment value” and one for the result you want.
  
CM_Date_Adjustment__c (a number)
IF(AND(VALUE(CM_Test_Date_Field__c)<=5,NOW() < DATETIMEVALUE(TODAY())+(19/24)), 0, CASE(CM_Test_Date_Field__c, "5", 3, "6", 2, "7", 2, 1))

<Your original field>
TEXT(YEAR(TODAY()+CM_Date_Adjustment__c)) + LPAD(TEXT(MONTH(TODAY()+CM_Date_Adjustment__c)),2,"0") + LPAD(TEXT(DAY(TODAY()+CM_Date_Adjustment__c)),2,"0")

I haven’t tested this but hopefully the syntax is 100% correct—I am sure you can see the logic though. [ PS: Is Sunday really “+2”? ]

Hope this is useful to you.
MellowRenMellowRen
Odd. I seem to have lost the ability to edit my own posts :-/  Even within seconds of posting.

Apparently bolding inside a code snippet is fine in preview but once posted gets coverted to pseudo-HTML. Sorry about that. I was just trying to separate the two formulas.