You need to sign in to do that
Don't have an account?
Kamatchi Devi Sargunanathan
Need to calculate the difference between the two times (created as text fields)
Hi all,
I have two text fields WorkStartTime__c and WorkEndTime__c. And need to create a formula field that returns a the difference between these two times as a decimal number to specify how many hours a person worked.
For Eg,
 WorkStartTime__c can be like 9:00 AM (or) 09:00 AM (or) 09:30 AM and so on
 WorkEndTime__c can be like 5:00 PM (or) 05:00 PM (or) 05:30 PM and so on
 Difference should be like 8.00 all the above scenarios.
Please help me how to write a formula to split a string and calculate the time difference
Thanks in Advance.
Hi Agi,
Really you are great, the formula works and I appriciated your hard work. But there are some scenarios it would fail.
For example, 9:30 AM to 5:00 PM will return me 7.5 exactly.
But, it will throw #Error for the scenario 09:30 AM to 11:00 AM. So, we should consider the AMAM / AMPM / PMAM
So, i have gone to Javascript to do this option. Following is the script to find the difference between the two text time fields.
So, I'm getting Hours and Minutes exactly for all scenarios, and storing these values in to the two field as Hours__c and Minutes__c in my sObject. And using the following formula(return type as Number(2,0)) to use make it as number outcome as difference between these two time(text) fields.
VALUE( TEXT( Hours__c ) + '.'+ TEXT( Minutes__c ))
This may help some one else in the future....!
Please give kudos by clicking on the star icon, if you found this answer as helpful.
All Answers
Hi,
Please try the following way.
Please mark my answer as a solution if this works for you. If it was helpful so it is available to others as a proper solution.
Thanks
Subhani
Salesforce Certified Developer
www.mydbsync.com
Thanks for your reply, but i need to calculate time diffrerence by using two text fields.
But your formula will not be used for calculation. You cannot directly subtract two text field. If you do so, you will get error.
Please help in triming the text values and how to calculate the hours and minutes exactly?
Hi,
you can use the following, but it only works when the text fields are in the correct fomat ( h:mm AM/PM)
TEXT(
(Value(LEFT(text_end__c, 1))*60 +
Value(MID(text_end__c,3, 2)) +
IF (Right (text_end__c, 2)= "PM", 720, 0)

(Value(LEFT(text_start__c, 1))*60+
Value( MID(text_start__c,3, 2)) +
IF (Right (text_start__c,2)= "PM", 720, 0)))/60)
Hi,
if you set up a validation rule to ensure that both fields are in the same format ( 9:00 AM (or) 09:00 AM),
OR(
AND(
VALUE(LEFT(text_end__c, 1))=0,
VALUE(LEFT(text_start__c, 1)) <> 0),
AND(
VALUE(LEFT(text_end__c, 1)) <> 0 ,
VALUE(LEFT(text_start__c, 1))= 0))
the follwing works for both ( WorkStartTime__c 9:00 AM and WorkEndTime__c 5:00 PM / WorkStartTime__c 09:00 AM and WorkEndTime__c 05:00 PM)
IF(
AND(
VALUE(LEFT(text_end__c, 1))=0,
VALUE(LEFT(text_start__c, 1))=0),
(TEXT(
(Value(RIGHT(LEFT(text_end__c, 2),1))*60 +
Value(MID(text_end__c,4, 2)) +
IF (Right (text_end__c, 2)= "PM", 720, 0)

(Value(RIGHT(LEFT(text_start__c, 2),1))*60+
Value( MID(text_start__c,4, 2)) +
IF (Right (text_start__c,2)= "PM", 720, 0)))/60)),
(TEXT(
(Value(LEFT(text_end__c, 1))*60 +
Value(MID(text_end__c,3, 2)) +
IF (Right (text_end__c, 2)= "PM", 720, 0)

(Value(LEFT(text_start__c, 1))*60+
Value( MID(text_start__c,3, 2)) +
IF (Right (text_start__c,2)= "PM", 720, 0)))/60)))
best regards,
Agi
Hi Agi,
Really thanks for your suggestions. I already tried these type of ways to check the time difference.But what happend if there is any mismatch in the timings?
For, example 9:00 AM and 08:00 PM is used with this formula, then it will become #Error! as output in your formula.
Also, I cannot set a validation for writing the time, because my requirement is to do without any warnings.
Anyways, Thanks for your help.
Hi,
This one should work for
9:00 AM and 08:00 PM
09:00 AM and 8:00 PM
09:00AM and 08:00 PM
9:00 AM and 8:00 PM
IF( AND(
VALUE(LEFT(text_end__c, 1))=0,
VALUE(LEFT(text_start__c, 1))=0),
(TEXT(
(Value(RIGHT(LEFT(text_end__c, 2),1))*60 +
Value(MID(text_end__c,4, 2)) +
IF (Right (text_end__c, 2)= "PM", 720, 0)

(Value(RIGHT(LEFT(text_start__c, 2),1))*60+
Value( MID(text_start__c,4, 2)) +
IF (Right (text_start__c,2)= "PM", 720, 0)))/60)),
IF( AND(
VALUE(LEFT(text_end__c, 1))=0,
VALUE(LEFT(text_start__c, 1)) <> 0),
(TEXT(
(Value(RIGHT(LEFT(text_end__c, 2),1))*60 +
Value(MID(text_end__c,4, 2)) +
IF (Right (text_end__c, 2)= "PM", 720, 0)

(Value(LEFT(text_start__c, 1))*60+
Value( MID(text_start__c,3, 2)) +
IF (Right (text_start__c,2)= "PM", 720, 0)))/60)),
IF( AND(
VALUE(LEFT(text_end__c, 1))<> 0,
VALUE(LEFT(text_start__c, 1)) = 0),
(TEXT(
(Value(LEFT(text_end__c, 1))*60 +
Value(MID(text_end__c,3, 2)) +
IF (Right (text_end__c, 2)= "PM", 720, 0)

(Value(RIGHT(LEFT(text_start__c, 2),1))*60+
Value( MID(text_start__c,4, 2)) +
IF (Right (text_start__c,2)= "PM", 720, 0)))/60)),
(TEXT(
(Value(LEFT(text_end__c, 1))*60 +
Value(MID(text_end__c,3, 2)) +
IF (Right (text_end__c, 2)= "PM", 720, 0)

(Value(LEFT(text_start__c, 1))*60+
Value( MID(text_start__c,3, 2)) +
IF (Right (text_start__c,2)= "PM", 720, 0)))/60)))))
Hi Agi,
Really you are great, the formula works and I appriciated your hard work. But there are some scenarios it would fail.
For example, 9:30 AM to 5:00 PM will return me 7.5 exactly.
But, it will throw #Error for the scenario 09:30 AM to 11:00 AM. So, we should consider the AMAM / AMPM / PMAM
So, i have gone to Javascript to do this option. Following is the script to find the difference between the two text time fields.
So, I'm getting Hours and Minutes exactly for all scenarios, and storing these values in to the two field as Hours__c and Minutes__c in my sObject. And using the following formula(return type as Number(2,0)) to use make it as number outcome as difference between these two time(text) fields.
VALUE( TEXT( Hours__c ) + '.'+ TEXT( Minutes__c ))
This may help some one else in the future....!
Please give kudos by clicking on the star icon, if you found this answer as helpful.