Ask Search:
Jamie McKechnieJamie McKechnie 

Formula to calculate difference between two time stamped fields?

Firstly thanks to the community for previous answers supplied.
I have a number of fields that have been date time stamped as a result of status changes in another field, ie:
Order Status changed to "Call Accepted'  - Travel Time Start Field stamped using 'NOW()' fomula =
29/12/2011 2:20 PM
Order Status changed to "Arrived on Site" - Travel End Time field stamped using 'NOW()' formula =
29/12/2011 2:40 PM

I want to create a custom field using a formula to calculate the difference between these two times.

Any advice or support that the community can provide will be appreciated.

Steve MolisSteve Molis
Sp basically your field would be a datatype Formula(Number) and the formula would be:

Travel_End_Time__c - Travel_Start_Time__c  

the result would be NN days.  

What format do you want your result to appear as?
Srinivasa Kumar GullapalliSrinivasa Kumar Gullapalli
just use 
Create your new custom field, say, Total_Travel_Time.
Select the data type as formula and use the below mentioned formula.
Travel_End_Time_c - Travel_Start_Time_c.
Nebojsa ZgonjaninNebojsa Zgonjanin
Here you go:

Formula - Weekend Days or Workdays Calculation


Knowledge Article Number: 102787


Description

Formula - Weekend Days or Workdays Calculation




Resolution

Purpose:
Calculate the number of days between two dates while excluding weekends or excluding weekdays.

Formulas provided:

  1. Weekday Count Formula
  2. Weekend Days Count Formula

Steps to create:

  1. Create a formula field that returns a number.
  2. Paste in one of the two formulas.
  3. Replace StartDate__c and EndDate__c with your custom field values.
  4. If using Date/Time fields
    • Replace with DATEVALUE(YourCustomDateTime__c) instead.

Assumptions/Limitations/Background:

  • Weekdays are defined as Mon-Fri and weekends as Sat & Sun
  • Holidays are not addressed
  • June 6, 1985 is a long distant Monday used as a reference point
  • The result will include BOTH the START and END dates.
    • Mon-Sun is counted as 5 Weekdays and 2 Weekend Days.
    • Mon-Fri is NOT (Fri subtract Mon) = 4 Elapsed Days.
    • Sat-Sun is NOT (Sun subtract Sat) = 1 Elapsed Day.
  • If you use another Formula field as the start or end dates you MAY hit a compilation limit.
    • Workaround - Use workflow rules to save the output of the formula fields into a regular date field.
  • Be sure to TEST the formulas FIRST.

Weekday Count Formula:

  CASE(MOD( StartDate__c - DATE(1985,6,24),7), 

  0 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
  1 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
  2 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
  3 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
  4 , CASE( MOD( EndDate__c - StartDate__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
  5 , CASE( MOD( EndDate__c - StartDate__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
  6 , CASE( MOD( EndDate__c - StartDate__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
  999) 
  + 
  (FLOOR(( EndDate__c - StartDate__c )/7)*5) 


Weekend Days Count Formula:

  CASE(MOD( StartDate__c - DATE(1985,6,24),7),
  0 , CASE( MOD( EndDate__c - StartDate__c, 7),1,0,2,0,3,0,4,0,5,1,6,2,0),
  1 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,0,3,0,4,0,5,2,2),
  2 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,0,3,1,2),
  3 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,1,2),
  4 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,1,2),
  5 , CASE( MOD( EndDate__c - StartDate__c, 7),0,1,2),
  6 , CASE( MOD( EndDate__c - StartDate__c, 7),6,2,1),
  999)
  +
  (FLOOR(( EndDate__c - StartDate__c )/7)*2)


Jamie McKechnieJamie McKechnie
Thanks everyone for the answers that you provided.
I did not clearly state the type of result I was after which is the minutes difference between the two time stamps.
In the example in my question the start time was stamped as 2:20pm and the end time as 2:40pm on the same day so the total travel time would be 20 minutes and this is the answer I am after.
If I simply subtract one field from the other I end up with the result of 0.01 so either my calculation is wrong and I need to make further calculations to end up with minutes or my field format of number is wrong and I need a different type.

Steve MolisSteve Molis
Okay the result will be the number of Days, so you'll need to amend your formula by converting that number from NN Days to NNNN Minutes.  So if the result is 1.0 that equals 1,440 minutes.  

  (Travel_End_Time__c - Travel_Start_Time__c) * 1440  
Srinivasa Kumar GullapalliSrinivasa Kumar Gullapalli
Yes, what SteveMo stated is correct.
You have got  the correct answer. But a day has 1440 mins. So just multiply with 1440 as SteveMo said.
Steve MolisSteve Molis
@Jamie,
 are you all set with this or do you still need help?
Jamie McKechnieJamie McKechnie
Thanks everyone for your answers, problem is resolved now.
Steve MolisSteve Molis
What ended up being the solution?
Rahul MitraRahul Mitra
Hello All,

I am facing a similar issue where i have got two DateTime fields namely, Start_time__c and End_time__c. I want to create a new formula field to display the time duration between the two. However, i dont want the result only in minutes. I was looking to get the result in X Days X Hours X Minutes format. Any help on how that can be achieved?

Thanks,
Rahul