Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
George Yang 17George Yang 17 

DateTime formula error when the time string is after 8:00 pm

Hi,
I create a formula field with the following code:

DATETIMEVALUE(Text( CDC_Event_EDate__c) +" "+
IF( RIGHT (Text( CDC_Event_ETime__c ),2)=="PM",
IF(LEFT (Text( CDC_Event_ETime__c ),2)=="12",
TEXT(VALUE(LEFT(Text( CDC_Event_ETime__c), 2))+ 4),
TEXT(VALUE(LEFT(Text( CDC_Event_ETime__c), 2))+ 16)),
TEXT(VALUE(LEFT(Text( CDC_Event_ETime__c ), 2))+ 4)
)+
":" + MID(text(CDC_Event_ETime__c), 4,2)+ ":00")

The CDC_Event_EDate__c is a Date type and the value  is from Salesforce standard date picker.
The CDC_Event_ETime__c field is a string format as
....
08:00 AM
08:30 AM
...
08:00 PM
08:30 PM
09:00 PM
....
Everything is working fine EXCEPT of the time is after 08:00 PM the formula gives error. I could not figure out what is going wrong, Can you help me?  I know there are simple formula to replace the above one but I have tried some but get the same result.
thanks,

George
Krishna Kumar 148Krishna Kumar 148
May be due the return type. Are using something Date related field for this. After the "TEXT(VALUE(LEFT(Text( CDC_Event_ETime__c), 2))+ 4)," it might be adding  09+4, for a 24HR based timings this could work but you might be using 12HR clock based on AM and PM.

So if this cross 12:59, then it could turn to be invalid time format. Could be the issue you are facing.