+ Start a Discussion
Shane QuiringShane Quiring 

Change a text time value into a numerical value.

Ok I am trying to change a time value, in a text field ("10:00") into a numerical value. Currently I keep running into an error when I place in a " : " and use the "Value" function in my formula.

So here is the basic concept: The sales rep sets their meeting time (date and time in two separate fields, text field for time and date field for date) with a customer, depending on the time and the day, will depend on when my pricing team will need to get the pricing back to the sales rep. For example if the meeting date and time is on the 12th of May and the Meeting time is at 1pm, then the pricing team will need to get the pricing to the sales rep at 12pm on the 12th of May. But if the meeting time is before 11am then the pricing team need to get to them a day prior. Of course this all has to happen on the business day. everything works fine until you use the " : " for the time value (naturally we are using military time aka 24hr clock instead of the 12hr clock, makes my life easier), but if I use a " . ", for example "12.15" instead of "12:15" the formula works like a charm.

So here is my question, wth is up with the " : "?

Of course they would like to use the " : " instead of the " .". Any suggestions? Below is my two formula’s that I am using in a workflow rule field update (in order to get around the holidays I created an additional formula that caculates the day of the week):

Supplier Due Time:
IF( Meeting_Day_of_the_Week__c = "Monday",
Text(Value(Meeting_Time__c)-1),
IF( Meeting_Day_of_the_Week__c = "Tuesday" &&
Value(Meeting_Time__c) <= 11,
"4pm",
IF( Meeting_Day_of_the_Week__c = "Tuesday" &&
Value(Meeting_Time__c) > 11,
Text(Value(Meeting_Time__c)-1),
If (Meeting_Day_of_the_Week__c = "Wednesday" &&
Value(Meeting_Time__c) <= 11,
"4pm",
IF( Meeting_Day_of_the_Week__c = "Wednesday" &&
Value(Meeting_Time__c) > 11,
Text(Value(Meeting_Time__c)-1),
If (Meeting_Day_of_the_Week__c = "Thursday" &&
Value(Meeting_Time__c) <= 11,
"4pm",
IF( Meeting_Day_of_the_Week__c = "Thursday" &&
Value(Meeting_Time__c) > 11,
Text(Value(Meeting_Time__c)-1),
If (Meeting_Day_of_the_Week__c = "Friday" &&
Value(Meeting_Time__c) <= 11,
"4pm",
IF (Meeting_Day_of_the_Week__c = "Friday" &&
Value(Meeting_Time__c) > 11,
Text(Value(Meeting_Time__c)-1),
Null
)))))))))

Supplier Date:
IF( Meeting_Day_of_the_Week__c = "Monday",
Meeting_Date__c,
IF( Meeting_Day_of_the_Week__c = "Tuesday" &&
Value(Meeting_Time__c) <= 11,
Meeting_Date__c -1,
IF( Meeting_Day_of_the_Week__c = "Tuesday" &&
Value(Meeting_Time__c) > 11,
Meeting_Date__c,
If (Meeting_Day_of_the_Week__c = "Wednesday" &&
Value(Meeting_Time__c) <= 11,
Meeting_Date__c -1,
IF( Meeting_Day_of_the_Week__c = "Wednesday" &&
Value(Meeting_Time__c) > 11,
Meeting_Date__c,
If (Meeting_Day_of_the_Week__c = "Thursday" &&
Value(Meeting_Time__c) <= 11,
Meeting_Date__c -1,
IF( Meeting_Day_of_the_Week__c = "Thursday" &&
Value(Meeting_Time__c) > 11,
Meeting_Date__c,
If (Meeting_Day_of_the_Week__c = "Friday" &&
Value(Meeting_Time__c) <= 11,
Meeting_Date__c -1,
IF( Meeting_Day_of_the_Week__c = "Friday" &&
Value(Meeting_Time__c) > 11,
Meeting_Date__c,
Null
)))))))))
Best Answer chosen by Shane Quiring
AmitAmit (Salesforce Developers) 
Hello,

You need to first SPLIT the time string into two strings removing " : " then concatenate both strings together and then use VALUE to convert the final string to number.

Please refer following links for more information :

http://www.salesforce.com/us/developer/docs/dbcom_apex250/Content/apex_methods_system_string.htm

http://www.salesforce.com/us/developer/docs/api/Content/primitive_data_types.htm

http://help.salesforce.com/help/pdfs/en/formula_date_time_tipsheet.pdf

Thanks,
Amit Bhardwaj