 ShowAll Questionssorted byDate Posted 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 Best Answer chosen by Admin (Salesforce Developers)  Kamatchi Devi Sargunanathan

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 AM-AM / AM-PM / PM-AM

So, i have gone to Javascript to do this option. Following is the script to find the difference between the two text time fields.

```<script type="text/javascript">
function Converttimeformat() {
// var time = \$("#starttime").val();
var time = document.getElementById('txttime').value;
var hrs = Number(time.match(/^(\d+)/));
var mnts = Number(time.match(/:(\d+)/));
var format = time.match(/\s(.*)\$/);
if (format == "PM" && hrs < 12) hrs = hrs + 12;
if (format == "AM" && hrs == 12) hrs = hrs - 12;
var hours =hrs.toString();
var minutes = mnts.toString();
if (hrs < 10) hours = "0" + hours;
if (mnts < 10) minutes = "0" + minutes;

var date1 = new Date();
date1.setHours(hours );
date1.setMinutes(minutes);

var time = document.getElementById('txttime1').value;
var hrs = Number(time.match(/^(\d+)/));
var mnts = Number(time.match(/:(\d+)/));
var format = time.match(/\s(.*)\$/);
if (format == "PM" && hrs < 12) hrs = hrs + 12;
if (format == "AM" && hrs == 12) hrs = hrs - 12;
var hours = hrs.toString();
var minutes = mnts.toString();
if (hrs < 10) hours = "0" + hours;
if (mnts < 10) minutes = "0" + minutes;
var date2 = new Date();
date2.setHours(hours );
date2.setMinutes(minutes);

var diff = date2.getTime() - date1.getTime();

var hours = Math.floor(diff / (1000 * 60 * 60));
diff -= hours * (1000 * 60 * 60);

var mins = Math.floor(diff / (1000 * 60));
diff -= mins * (1000 * 60);
alert( hours + " hours : " + mins + " minutes : " );

}
</script>```

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....! Subhani_SFDC

Hi,

(WorkEndTime__c - WorkStartTime__c) * 1440

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 Kamatchi Devi Sargunanathan
Hi Shubhani,

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. Agi

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) Agi

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 Kamatchi Devi Sargunanathan

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. Agi

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))))) Kamatchi Devi Sargunanathan

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 AM-AM / AM-PM / PM-AM

So, i have gone to Javascript to do this option. Following is the script to find the difference between the two text time fields.

```<script type="text/javascript">
function Converttimeformat() {
// var time = \$("#starttime").val();
var time = document.getElementById('txttime').value;
var hrs = Number(time.match(/^(\d+)/));
var mnts = Number(time.match(/:(\d+)/));
var format = time.match(/\s(.*)\$/);
if (format == "PM" && hrs < 12) hrs = hrs + 12;
if (format == "AM" && hrs == 12) hrs = hrs - 12;
var hours =hrs.toString();
var minutes = mnts.toString();
if (hrs < 10) hours = "0" + hours;
if (mnts < 10) minutes = "0" + minutes;

var date1 = new Date();
date1.setHours(hours );
date1.setMinutes(minutes);

var time = document.getElementById('txttime1').value;
var hrs = Number(time.match(/^(\d+)/));
var mnts = Number(time.match(/:(\d+)/));
var format = time.match(/\s(.*)\$/);
if (format == "PM" && hrs < 12) hrs = hrs + 12;
if (format == "AM" && hrs == 12) hrs = hrs - 12;
var hours = hrs.toString();
var minutes = mnts.toString();
if (hrs < 10) hours = "0" + hours;
if (mnts < 10) minutes = "0" + minutes;
var date2 = new Date();
date2.setHours(hours );
date2.setMinutes(minutes);

var diff = date2.getTime() - date1.getTime();

var hours = Math.floor(diff / (1000 * 60 * 60));
diff -= hours * (1000 * 60 * 60);

var mins = Math.floor(diff / (1000 * 60));
diff -= mins * (1000 * 60);
alert( hours + " hours : " + mins + " minutes : " );

}
</script>```

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....!