• Tracy Newquist
  • NEWBIE
  • 0 Points
  • Member since 2016

  • Chatter
    Feed
  • 0
    Best Answers
  • 0
    Likes Received
  • 0
    Likes Given
  • 1
    Questions
  • 0
    Replies
This is what I have so far.  Everything is working fine except the AM and PM is only returning "AM" on every conversion. Also don't know how to solve for AM and PM when the time changes between PST and PDT. 

CASE( MOD( DATEVALUE(Event_Date_Start_Time__c) - DATE(1900, 1, 7), 7), 0, "Sunday", 1, "Monday", 2, "Tuesday", 3, "Wednesday", 4, "Thursday", 5, "Friday", 6, "Saturday","Error")
+", "+

CASE(MONTH(DATEVALUE( Event_Date_Start_Time__c )), 
1,"January", 
2,"February", 
3,"March", 
4,"April", 
5,"May", 
6,"June", 
7,"July", 
8,"August", 
9,"September", 
10,"October", 
11,"November",
12,"December",
NULL) 

+ ' ' + 
TEXT(DAY(DATEVALUE(Event_Date_Start_Time__c ))) 
+ ', ' + 
TEXT(YEAR(DATEVALUE(Event_Date_Start_Time__c )))
+ '  ' +

/*Daylight Savings*/
if( 
((Datevalue(Event_Date_Start_Time__c ) >= Date(2016,3,13)) && (Datevalue(Event_Date_Start_Time__c ) <= DATE(2016,11,6))) || 
((Datevalue(Event_Date_Start_Time__c ) >= Date(2017,3,12)) && (Datevalue(Event_Date_Start_Time__c ) <= DATE(2017,11,5))) || 
((Datevalue(Event_Date_Start_Time__c ) >= Date(2018,3,11)) && (Datevalue(Event_Date_Start_Time__c ) <= DATE(2018,11,4))) 
,

/*Append 24 HR GMT TO 12 hr PDT (GMT-7)(Daylight Savings Time)*/
TEXT(CASE(VALUE(MID(TEXT( Event_Date_Start_Time__c ), 12, 2)), 
24,5, 23,4, 22,3, 21,2, 20,1, 19,12, 18,11, 17,10, 16,9, 15,8, 14,7, 13,6, 12,5, 11,4, 10,3, 9,2, 8,1, 7,12, 6,11, 5,10, 4,9, 3,8, 2,7, 1,6, NULL))
,

/*Append 24 HR GMT TO 12 hr PST (GMT-8)*/
TEXT(CASE(VALUE(MID(TEXT( Event_Date_Start_Time__c ), 12, 2)), 
24,4, 23,3, 22,2, 21,1, 20,12, 19,11, 18,10, 17,9, 16,8, 15,7, 14,6, 13,5, 12,4, 11,3, 10,2, 9,1, 8,12, 7,11, 6,10, 5,9, 4,8, 3,7, 2,6, 1,5,NULL))

+
/*Append the minutes*/
mid(text(Event_Date_Start_Time__c ),14,3)+ " "+ 

/*Adds AM/PM */ 
if((value(mid(text(Event_Date_Start_Time__c ),12,2))<7 )&& (value(mid(text(Event_Date_Start_Time__c ),12,2))>=19)," PM", " AM")