I have a picklist called Preferred Start Time which has values (12:00 AM,12.30 AM,01:00 AM,0100 AM....11:00 PM,11:30 PM) for the user to select a start time.
There is another number field called "Estimated duration time to complete job" to enter the number of hours to capture the estimated hours.
I have a Time field called "End Time" which will be the addition of "Preferred Start Time" + "Estimated duration time to complete job".
Since we cannot add the Text value from the Start time picklist and the number field I created another field to convert the picklist value to time value with the following formula
/* comes in as "2:15 PM" */
TIMEVALUE( /* figure out hours */ TEXT(VALUE(LEFT(LPAD(TEXT(Preferred_Start_Time__c), 8,"0"), 2)) + IF(RIGHT(TEXT(Preferred_Start_Time__c), 2) == "PM",12,0))&
":"&
/* figure out minutes */ RIGHT(SUBSTITUTE(SUBSTITUTE(TEXT(Preferred_Start_Time__c), " AM", ""), " PM", ""), 2)& ":00.000" )
The formula is converting only some values in the Start time picklist.
The field is blank when I select any values from 12:00 AM to 09:30 AM times .
It is converting when I select any PM values except 12:00 PM and 12:30 PM.
Could anyone advise what I am doing wrong or a better workaround
Hi
IF(RIGHT(TEXT(Preferred_Start_Time__c), 2) == "AM",
CASE(VALUE(LEFT(LPAD(TEXT(Preferred_Start_Time__c), 8,"0"), 2)),
12,"00",
TEXT(VALUE(LEFT(LPAD(TEXT(Preferred_Start_Time__c), 8,"0"), 2))) ),
CASE(VALUE(LEFT(LPAD(TEXT(Preferred_Start_Time__c), 8,"0"), 2)),
12,"12",
TEXT(VALUE(LEFT(LPAD(TEXT(Preferred_Start_Time__c), 8,"0"), 2)) + 12)
)
)
& ":"& RIGHT(SUBSTITUTE(SUBSTITUTE(TEXT(Preferred_Start_Time__c), " AM", ""), " PM", ""), 2)& ":00.000"
