2

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)
Improve this question
2
  • 1
    Can you use the first line as an example and tell us what you’re trying to do? I’d like to help but there are a lot of layers to the formula.
    – Allison Letts
    Dec 17, 2019 at 21:47
  • @AllisonLetts I edited my original question for formatting and included the task description.
    – Tommy C.
    Dec 19, 2019 at 22:04

1 Answer 1

Reset to default
2

First, a note: working with DateTime fields in workflow field updates faces major hurdles, especially around time zones and Daylight Savings. If I understand correctly what you're after here, this is not really a good job for a workflow field update. IF all users are in the same time zone and you're willing to have your calculations be off by an hour during Standard time, then you can do it.

Second: simplify the calculation of the Date part throughout. Replace each instance of

TEXT(YEAR(DATEVALUE(NOW()))) + "-" + LPAD(TEXT(MONTH(DATEVALUE(NOW()))), 2, "0") + "-" + LPAD(TEXT(DAY(DATEVALUE(NOW()))), 2, "0")

with TEXT(TODAY()).

For the time part, we need to know the field type and expected content of the User.*day_Start_of_Day__c fields and Lead_Score_ID__r.No_Activity_Reassignment_Hours__c from which you're trying to compose the time portion. If you update your question with that info, I'll update this answer. What you need for the time portion is a string representing the time in GMT that looks like: 12:00:00. You're converting them with TEXT(), which suggests that they're numbers. So you're probably missing the colons at a minimum, and maybe other parts too. A best-case scenario guess is that the first represents an hour when the responsible User's workday starts and the latter an integer number of whole hours that the user has to get something done after the start of their day. In that case, something like 

IF((WEEKDAY(TODAY()) = 1 && !ISBLANK(Owner:User.Monday_Start_of_Day__c)),
   TEXT(TODAY() + 
       FLOOR((User.Monday_Start_of_Day__c + Lead_Score_ID__r.No_Activity_Reassignment_Hours__c + 6) /24)) +
    " " + 
    LPAD(
       TEXT(
           MOD(User.Monday_Start_of_Day__c + Lead_Score_ID__r.No_Activity_Reassignment_Hours__c + 6, 24)
           )
        ,2,"0") + 
    ":00:00")

for each of your current lines could work. The + FLOOR((User.Monday_Start_of_Day__c + Lead_Score_ID__r.No_Activity_Reassignment_Hours__c + 6) /24)) adds the number of whole days represented by the total hours, including 6 for the MDT/GMT offset. And MOD(User.Monday_Start_of_Day__c + Lead_Score_ID__r.No_Activity_Reassignment_Hours__c + 6, 24) gives the number of hours into the day (0-24) for the time portion.

This assumes:

  1. that all users are in US Mountain time. It will be one hour too early during the 4.5 mos of Standard time.
  2. That those two fields will always have integers.

Hope that helps point you in the right direction.

Improve this answer
4
  • Yeah, this task in general is making my hair fall out and I figured when I started it that time zones and Daylight Savings would be difficult to account for. Thank you for such an informative response, you have certainly pointed me in a much more right direction. 1. All of our users are in the same timezone (Mountain Standard Time) 2. *day_Start_of_Day__c and No_Activity_Reassignment_Hours__c are both number fields Above (answering Allison L's Q) is this task's description; if not a field update, what is your suggested approach to complete this task? An Apex Trigger?
    – Tommy C.
    Dec 17, 2019 at 23:53
  • 1
    Apex has functions for dates and times that are much more robust than what's in fornula functions, for sure. But if everybody's in the same TZ, then the apporach above might be adequate. You could "account" for DST by replacing the hard-coded 6 for the GMT offset with a reference to a Custom Metadata Type record or a Hierachy Custom Setting field and then manually change it, along with your smoke detector batteries, In Nov & May.
    – Thomas Taylor
    Dec 18, 2019 at 0:04
  • Thank you; I should have read these Salesforce Help docs a bit more carefully... "We recommend using Apex for transactions that require converting between Date/Time values and Text or Date values." Also, so sorry, I informed you incorrectly, the *day_Start_of_Day__c field is a Time field, which makes things considerably more difficult; I've spent the day trying to finagle this formula's values into Unix Time, is this a valid way to handle the time and number values in order to make the formula work?
    – Tommy C.
    Dec 18, 2019 at 22:46
  • 1
    If they're all on the hour, or the hour is adequately close for this task, then HOUR(User.Monday_Start_of_Day__c) will do the trick without a lot more overhead.
    – Thomas Taylor
    Dec 18, 2019 at 23:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .