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
Satya413Satya413 

Convert Text to Time

Hi All,

I have two custom field's of text datatype (Start_Time__c and End_Time__c). I want to put a validation rule so that the start time is always less than end time. Does anyone know how to proceed with this. 

Thank you,
Satya
Best Answer chosen by Satya413
William TranWilliam Tran
Assuming your time format is ##:## AM  like  09:00 AM or 11:45 PM

use this formula

Thx
  
IF(RIGHT(Start_time__c, 2) ="AM" &&  RIGHT(End_time__c , 2) ="PM", false,
IF(RIGHT(Start_time__c, 2) ="PM" &&  RIGHT(End_time__c , 2) ="AM", true,
IF( Value(substitute(substitute(LEFT(Start_time__c , 5),'12:','00:'),':','')) > Value(substitute(substitute(LEFT(End_time__c , 5),'12:','00:'),':','')),true, false)))

 

All Answers

William TranWilliam Tran
What is the text format for the date?  MM/DD/YYYY or MM-DD-YYYY or something else?

Thx
Satya413Satya413
Hi William, 

Thanks for your response. There is no date involved. Say for example, I enter 09:00 AM for the Start_Time__c field which is text datatype. I want the validation to throw an error if someone enters the End_Time__c to be less than 9 AM. 

So basically, I want the time that is entered in text field to be converted to time form so that we can compare which is ahead. I am not sure if this is possible but this is what I want to achieve. 

Thank you,
Satyanarayana
William TranWilliam Tran
Assuming your time format is ##:## AM  like  09:00 AM or 11:45 PM

use this formula

Thx
  
IF(RIGHT(Start_time__c, 2) ="AM" &&  RIGHT(End_time__c , 2) ="PM", false,
IF(RIGHT(Start_time__c, 2) ="PM" &&  RIGHT(End_time__c , 2) ="AM", true,
IF( Value(substitute(substitute(LEFT(Start_time__c , 5),'12:','00:'),':','')) > Value(substitute(substitute(LEFT(End_time__c , 5),'12:','00:'),':','')),true, false)))

 
This was selected as the best answer
Satya413Satya413
Awesomee...Thank u so much.