Convert Military time in to Regular Time Using Formula Field.

Arun Garg   7/09/2017   Comment  

1. Here, I am using formula fields to convert military time (0200) in to regular time (2:00 AM).
2. I create two text fields which store In and Out time in Military Format.
3. Then I convert these field value in to Regular time by using text type formula fields.

Formula to convert Military time in Regular Time:

Firstly create two formula fields 'checkIN' and 'outTime' of text types.

For CheckIn:- 

API Name- We will use In time api here 'In_Time__c'

Formula:-  

IF( VALUE( LEFT( In_Time__c, 2 ) ) = 12 , "12" + ':'+RIGHT( In_Time__c, 2 ) + "PM", 
IF( VALUE( LEFT( In_Time__c, 2 ) ) = 0 , "12" + ':'+RIGHT( In_Time__c, 2 ) + "AM", 
IF( VALUE( LEFT( In_Time__c, 2 ) ) > 12, 
IF( VALUE( LEFT( In_Time__c, 2 ) ) < 22, 
"0" + TEXT( VALUE( LEFT( In_Time__c, 2 ) ) - 12 ) + ':'+RIGHT( In_Time__c, 2 ) + "PM", 
TEXT( VALUE( LEFT( In_Time__c, 2 ) ) - 12 ) + ':'+RIGHT( In_Time__c, 2 ) + "PM" ), 
LEFT( In_Time__c, 2 )+":"+RIGHT( In_Time__c, 2 )+ "AM" ) ) )

Result- In time Value- 0810 (Military Time) ----> 08:10 AM (Regular Time)

For OutTime:- 

API Name- We will use Out Time api here 'Out_Time__c'

Formula:- 

IF( VALUE( LEFT( Out_Time__c, 2 ) ) = 12 , "12" + ':'+RIGHT( Out_Time__c, 2 ) + "PM", 
IF( VALUE( LEFT( Out_Time__c, 2 ) ) = 0 , "12" + ':'+RIGHT( Out_Time__c, 2 ) + "AM", 
IF( VALUE( LEFT( Out_Time__c, 2 ) ) > 12, 
IF( VALUE( LEFT( Out_Time__c, 2 ) ) < 22, 
"0" + TEXT( VALUE( LEFT( Out_Time__c, 2 ) ) - 12 ) + ':'+RIGHT( Out_Time__c, 2 ) + "PM", 
TEXT( VALUE( LEFT( Out_Time__c, 2 ) ) - 12 ) + ':'+RIGHT( Out_Time__c, 2 ) + "PM" ), 
LEFT( Out_Time__c, 2 )+":"+RIGHT( Out_Time__c, 2 )+ "AM" ) ) )

Result- In time Value- 1400 (Military Time) ----> 02:00 PM (Regular Time)