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
Scott RussoScott Russo 

Was a Support Case created during the night shift? Monday to Friday? - Need help modifying existing code


All,
Our developer is no longer with us, and I need to modify this Case, Field formula.  I want the field to be set to a Y if it was created Monday - Friday, between the hours of 6:00 PM EST and 11:59 PM EST.  I can't figure out which part of this formula needs to be updated.
I believe this code was originally set for Sunday to Friday.
I appreciate your assistance. 
IF(
AND(
VALUE(LEFT(RIGHT(TEXT( CreatedDate ),9),2))>= 0,VALUE(LEFT(RIGHT(TEXT( CreatedDate ),9),2))<=11,CASE(MOD(DateValue(CreatedDate)-DATE(1900, 1, 7 ), 7 ), 1, 1, 2, 1, 3, 1, 4, 1, 0)=1), "Y",
IF(
AND(VALUE(LEFT(RIGHT(TEXT( CreatedDate ),9),2))<=4,VALUE(LEFT(RIGHT(TEXT( CreatedDate ),9),2))>=1,CASE(MOD(DateValue(CreatedDate)-DATE(1900, 1, 7 ), 7 ), 0, 1, 0)=1),"Y",
IF(
AND(VALUE(LEFT(RIGHT(TEXT( CreatedDate ),9),2))>=5,VALUE(LEFT(RIGHT(TEXT( CreatedDate ),9),2))<=12,CASE(MOD(DateValue(CreatedDate)-DATE(1900, 1, 7 ), 7 ), 5, 1, 0)=1),
"Y","N")
)
)

 
Best Answer chosen by Scott Russo
Saravanan Gengan 9Saravanan Gengan 9
CURRENT FORMULA LOGIC: 
MON,TUE,WED,THU - 12:00AM EST TO 06:59AM EST = "Y"
MON,TUE,WED,THU    - 07:00PM EST TO 11:59PM EST = "Y" 
FRI                - 12:00AM EST TO 07:59AM EST = "Y" 
SUN                - 08:00PM EST TO 11:59PM EST = "Y"
REST ALL "N" 

LOGIC FOR : MON,TUE,WED,THU,FRI    - 06:00PM EST TO 11:59PM EST = "Y" 
  
IF(
AND(
VALUE(LEFT(RIGHT(TEXT( CreatedDate  ),9),2))>= 0,
OR(VALUE(LEFT(RIGHT(TEXT( CreatedDate  ),9),2))<=4,VALUE(LEFT(RIGHT(TEXT( CreatedDate  ),9),2)) == 23),
CASE(MOD(DateValue(CreatedDate )-DATE(1900, 1, 7 ), 7 ), 1, 1, 2, 1, 3, 1, 4, 1,5, 1, 0)=1), 
"Y","N")

Please try and let me know. 

All Answers

Saravanan Gengan 9Saravanan Gengan 9
CURRENT FORMULA LOGIC: 
MON,TUE,WED,THU - 12:00AM EST TO 06:59AM EST = "Y"
MON,TUE,WED,THU    - 07:00PM EST TO 11:59PM EST = "Y" 
FRI                - 12:00AM EST TO 07:59AM EST = "Y" 
SUN                - 08:00PM EST TO 11:59PM EST = "Y"
REST ALL "N" 

LOGIC FOR : MON,TUE,WED,THU,FRI    - 06:00PM EST TO 11:59PM EST = "Y" 
  
IF(
AND(
VALUE(LEFT(RIGHT(TEXT( CreatedDate  ),9),2))>= 0,
OR(VALUE(LEFT(RIGHT(TEXT( CreatedDate  ),9),2))<=4,VALUE(LEFT(RIGHT(TEXT( CreatedDate  ),9),2)) == 23),
CASE(MOD(DateValue(CreatedDate )-DATE(1900, 1, 7 ), 7 ), 1, 1, 2, 1, 3, 1, 4, 1,5, 1, 0)=1), 
"Y","N")

Please try and let me know. 
This was selected as the best answer
Scott RussoScott Russo
Thank you. I will give it a try.  HAve a good weekend
Scott RussoScott Russo
Thank you Saravanan.  This worked.  Cheers