Skip to main content
Topics#Data ManagementFrage von Lawson Teo
Lawson Teo (Reform Clothing Co) fragte in #Data Management
24. Jan. 2018, 20:09

I am stumped with a time formula in process builder

This is doing my head in. I'm at GMT+11 so Leads are created by me (through Pardot) in my time, but I'm trying to configure a process to fire for our US office, which is GMT-8, and as I understand it, when you do a formula around time in PB, you've to calculate with GMT+0.

 

I'm trying to get a process to only fire between 5pm - 6:59am weekdays and all day Sat/Sun (GMT-8) I created a field "Created Date in GMT Time", which is Created Date (GMT+11) -11/24. And these are my criteria for the node, but they don't seem to be working:

 

Created Date less than:

 

CASE( 

 

MOD( DATEVALUE([Lead].Created_Date_in_GMT_Time__c)  - DATE(1900, 1, 7), 7), 

 

0, DATETIMEVALUE(TODAY()), 

 

1, DATETIMEVALUE(TODAY())+1/24,  

 

2, DATETIMEVALUE(TODAY())+1/24,  

 

3, DATETIMEVALUE(TODAY())+1/24,  

 

4, DATETIMEVALUE(TODAY())+1/24,  

 

5, DATETIMEVALUE(TODAY())+1/24,    

 

6, DATETIMEVALUE(TODAY()), 

 

DATETIMEVALUE(TODAY())

 

)

 

Created Date greater than or equals:

 

CASE( 

 

MOD( TODAY() - DATE(1900, 1, 7), 7), 

 

0, DATETIMEVALUE(TODAY()), 

 

1, DATETIMEVALUE(TODAY()),

 

2, DATETIMEVALUE(TODAY())-4/24,

 

3, DATETIMEVALUE(TODAY())-4/24,  

 

4, DATETIMEVALUE(TODAY())-4/24, 

 

5, DATETIMEVALUE(TODAY())-4/24, 

 

6, DATETIMEVALUE(TODAY())-4/24,  

 

DATETIMEVALUE(TODAY())

 

)

 

Anyone?
2 Antworten
  1. Steve Dodd (Seeking a new opportunity)
    25. Jan. 2018, 01:47

    Lawson,

     

    Give this a try...you can create a Checkbox formula field on the Lead object, and use it as part of the entry criteria in your process.

     

     

    Checkbox_Field__c   |   Equals   |   Boolean   |   True

     

    This formula will evaluate to TRUE if either of the below scenarios exist:

     

    1) Lead is created between 5:00pm and 6:59am (GMT - 8:00) on Monday, Tuesday, Wednesday, Thursday or Friday..and the day of the week lines up with (GMT - 8:00)

     

    Example:

     

    Lead is created by you on Saturday at 9:00am (GMT + 11:00) but the result is FALSE because it's 2:00pm (GMT - 8:00) on Friday, and even though it is a weekday, it falls outside of the specified time range.

     

    2) Lead is created between 12:00am (GMT - 8:00) on Saturday and 11:59pm (GMT - 8:00) on Sunday night

     

    Example:

     

    Lead is created by you anytime between 7:00pm (GMT + 11:00) on Saturday and at 6:59pm (GMT + 11:00) on Monday, and the result will be TRUE because it's either Saturday or Sunday in the U.S. (GMT - 8:00)

     

    Formula Data Type = Checkbox
    IF(
    OR(
    AND(
    CASE(MOD(DATEVALUE(CreatedDate - 19/24) - DATE(1900, 1, 6), 7),
    0, 0,
    1, 0,
    1)
    =1,
    OR(
    VALUE(MID(TEXT(CreatedDate - 3/24), 12, 2)) < 7,
    VALUE(MID(TEXT(CreatedDate - 3/24), 12, 2)) >= 17
    )
    ), 
    CASE(MOD(DATEVALUE(CreatedDate - 19/24)- DATE(1900, 1, 6), 7),
    0, 0,
    1, 0,
    1)
    =0
    ),
    TRUE,FALSE)

     

     
Antwort schreiben...
0/9000