I have a workflow that updates a field based on a users schedule.
The workflow action to update the field is telling me that it is computing an invalid date, I haven't been able to identify what the issue is but I have a suspicion I'm not formatting dates correctly; is there anyone that can suss out what is wrong with the following formula?
Edit: I'm only providing the Monday code blocks for sake of readability.
Workflow Action:
IF((WEEKDAY(TODAY()) = 1 && !ISBLANK(Owner:User.Monday_Start_of_Day__c)), DATETIMEVALUE(TEXT(YEAR(DATEVALUE(NOW()))) + "-" + LPAD(TEXT(MONTH(DATEVALUE(NOW()))), 2, "0") + "-" + LPAD(TEXT(DAY(DATEVALUE(NOW()))), 2, "0") +" "+ (TEXT(Owner:User.Monday_Start_of_Day__c) + TEXT(Lead_Score_ID__r.No_Activity_Reassignment_Hours__c))),
Workflow Rule that triggers field update:
IF(WEEKDAY(TODAY()) = 1 && Last_Assign_Date_Time__c < (DATETIMEVALUE(TEXT(Owner:User.Monday_End_of_Day__c)) - Lead_Score_ID__r.Hours_To_Convert_Lead_Before_End_of_Day__c),TRUE,FALSE)
||
IF(((WEEKDAY(DATEVALUE(Last_Assign_Date_Time__c))= 1) && (ISBLANK(Owner:User.Monday_Start_of_Day__c) && ISBLANK(Owner:User.Monday_End_of_Day__c))), TRUE, FALSE)
The task I'm trying to accomplish with the above Workflow Field Updates:
"If Lead Last Assigned Date Time is less than X (Hours to Convert Lead Before End of Day) hours before Owner End of Day OR if no schedule exists for the day a Lead is received, then, Inactivity Reassignment Date = Z (No Activity Reassignment Hours) hours after Owners next valid Start of Day."
Thank you for your time and expertise!
Update 12/19/19
Big Ups to @ThomasTaylor
Workflow Criteria:
IF(WEEKDAY(TODAY()) = 4 && Last_Assign_Date_Time__c < (DATETIMEVALUE(TEXT(Owner:User.Thursday_End_of_Day__c)) - Lead_Score_ID__r.Hours_To_Convert_Lead_Before_End_of_Day__c),TRUE,FALSE)
||
IF(((WEEKDAY(DATEVALUE(Last_Assign_Date_Time__c))= 4) && (ISBLANK(Owner:User.Thursday_Start_of_Day__c) && ISBLANK(Owner:User.Thursday_End_of_Day__c))), TRUE, FALSE)
- Last_Assign_Date_Time__c = Date/Time
- Owner:User.Thursday_Start_of_Day__c = Time
- Lead_Score_ID__r.No_Activity_Reassignment_Hours__c = Number(2,0)
Workflow Action:
IF((WEEKDAY(TODAY()) = 4 && !ISBLANK(Owner:User.Thursday_Start_of_Day__c)),
TEXT(TODAY() +
FLOOR((HOUR(Owner:User.Thursday_Start_of_Day__c) + Lead_Score_ID__r.No_Activity_Reassignment_Hours__c + 6) /24)) +
" " +
LPAD(
TEXT(
MOD(HOUR(Owner:User.Thursday_Start_of_Day__c) + Lead_Score_ID__r.No_Activity_Reassignment_Hours__c + 6, 24)
)
,2,"0") +
":00:00", NULL)