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
Kamatchi Devi SargunanathanKamatchi 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.

Best Answer chosen by Admin (Salesforce Developers) 
Kamatchi Devi SargunanathanKamatchi 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+)/)[1]);
var mnts = Number(time.match(/:(\d+)/)[1]);
var format = time.match(/\s(.*)$/)[1];
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;
//alert(hours + ":" + minutes);

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

var time = document.getElementById('txttime1').value;
var hrs = Number(time.match(/^(\d+)/)[1]);
var mnts = Number(time.match(/:(\d+)/)[1]);
var format = time.match(/\s(.*)$/)[1];
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;
//alert(hours+ ":" + minutes);
var date2 = new Date();
date2.setHours(hours );
date2.setMinutes(minutes);
//alert(date2);

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



Please give kudos by clicking on the star icon, if you found this answer as helpful.

 

All Answers

Subhani_SFDCSubhani_SFDC

Hi,

 

Please try the following way.

 

(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 SargunanathanKamatchi 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.

Please help in triming the text values and how to calculate the hours and minutes exactly?
AgiAgi

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

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

 

Anyways, Thanks for your help.

 
AgiAgi

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 SargunanathanKamatchi 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+)/)[1]);
var mnts = Number(time.match(/:(\d+)/)[1]);
var format = time.match(/\s(.*)$/)[1];
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;
//alert(hours + ":" + minutes);

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

var time = document.getElementById('txttime1').value;
var hrs = Number(time.match(/^(\d+)/)[1]);
var mnts = Number(time.match(/:(\d+)/)[1]);
var format = time.match(/\s(.*)$/)[1];
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;
//alert(hours+ ":" + minutes);
var date2 = new Date();
date2.setHours(hours );
date2.setMinutes(minutes);
//alert(date2);

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



Please give kudos by clicking on the star icon, if you found this answer as helpful.

 

This was selected as the best answer