Skip to main content
Topics#Data ManagementPregunta de Brent Downey
Brent Downey (Pushpay) preguntó en #Data Management
29 ene 2016, 21:46

Lead Escalation Start Date Formula

I need some assistance. I've got a formula working at about 99%, but I'm off somewhere and could use some assistance.

 

I've got a Date/Time formula field on the Lead called Lead Escalation Start which should return a specific Date/Time based on when the Lead was created. Here's how it should work:

  • If the Lead is created on a Saturday, set the Lead Escalation Start to be ~7AM on Monday.
  • If the Lead is created on a Sunday, set the Lead Escalation Start to be ~7AM on Monday.
  • If the Lead is created on Friday after 5PM, set the Lead Escalation Start to be ~7AM on Monday.
  • If the Lead is created after 5PM on a weekday other than Friday, the Lead Escalation Start Time is set to ~7AM the next day. If none of the conditions evaluate to true, the created date/time is assigned as the Lead Escalation Start.

Here's the formula:

 

IF(MOD(DATEVALUE(Dummy_Date_Time__c) - DATE(1985,7,1),7) = 5, 

 

DATETIMEVALUE(TEXT(DATEVALUE(Dummy_Date_Time__c +2)) & " 12:00:00"), 

 

IF(MOD(DATEVALUE(Dummy_Date_Time__c) - DATE(1985,7,1),7) = 6, 

 

DATETIMEVALUE(TEXT(DATEVALUE(Dummy_Date_Time__c +1)) & " 12:00:00"), 

 

IF(MOD(DATEVALUE(Dummy_Date_Time__c) - DATE(1985,7,1),7) = 4 && DATETIMEVALUE(TEXT(Dummy_Date_Time__c)) > DATETIMEVALUE(TEXT(DATEVALUE(Dummy_Date_Time__c)) & " 21:00:00"), 

 

DATETIMEVALUE(TEXT(DATEVALUE(Dummy_Date_Time__c +3)) & " 12:00:00"), 

 

IF(DATETIMEVALUE(Dummy_Date_Time__c) > DATETIMEVALUE(TEXT(DATEVALUE(Dummy_Date_Time__c)) & " 21:00:00"), 

 

DATETIMEVALUE(TEXT(DATEVALUE(Dummy_Date_Time__c +1)) & " 12:00:00"), 

 

Dummy_Date_Time__c))))

 

What I'm finding is that everything is working until we get to the last condition. If a Lead is created at 1AM on a Tuesday, the Lead Escalation Start is reflecting the Created Date/Time when it should actually be updated to ~7AM the same day.

 

Thanks for your help!
15 respuestas
  1. Steve Molis (You Owe Me a Beer, Inc.)
    1 feb 2016, 17:33
    Doh!  try this one instead:

     

     

    IF(MOD(DATEVALUE(DateTime_1__c) - DATE(1900,1,7),7) = 6, 
    DATETIMEVALUE(TEXT(DATEVALUE(DateTime_1__c + 2)) & " 12:00:00"), 
    IF(MOD(DATEVALUE(DateTime_1__c) - DATE(1900,1,7),7) = 0, 
    DATETIMEVALUE(TEXT(DATEVALUE(DateTime_1__c + 1)) & " 12:00:00"), 
    IF(MOD(DATEVALUE(DateTime_1__c) - DATE(1900,1,7),7) = 4 && DateTime_1__c > DATETIMEVALUE(TEXT(DATEVALUE(DateTime_1__c)) & " 21:00:00"), 
    DATETIMEVALUE(TEXT(DATEVALUE(DateTime_1__c + 3)) & " 12:00:00"), 
    IF(DateTime_1__c > DATETIMEVALUE(TEXT(DATEVALUE(DateTime_1__c)) & " 21:00:00"), 
    DATETIMEVALUE(TEXT(DATEVALUE(DateTime_1__c + 1)) & " 12:00:00"), 
    IF(DateTime_1__c < DATETIMEVALUE(TEXT(DATEVALUE(DateTime_1__c)) & " 12:00:00"), 
    DATETIMEVALUE(TEXT(DATEVALUE(DateTime_1__c)) & " 12:00:00"), 
    DateTime_1__c)))))

     

     
Escribir una respuesta...
0/9000