### Browse by categories

- All
- Collaboration
- Configuration & Data Management
- CPQ and Billing
- Customer Service & Support
- Desktop Integration
- Einstein Analytics
- Email Marketing
- Journey Management
- Mobile
- Mobile Messaging
- Packaging, Uploading & Installing Apps
- Reports & Dashboards
- Sales & Marketing
- Security
- Social Marketing
- Trailhead Challenges
- Additional Products

# Formula to calculate difference between two time stamped fields?

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 MolisSp 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 Gullapallijust 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 (Nash) ZgonjaninHere you go:

Description

Resolution
- Weekday Count Formula
- Weekend Days Count Formula

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

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

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)

## Formula - Weekend Days or Workdays Calculation

Knowledge Article Number: 102787Formula - Weekend Days or Workdays Calculation

Purpose:

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

Formulas provided:

Steps to create:

Assumptions/Limitations/Background:

Weekday Count Formula:

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

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 McKechnieThanks 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 MolisOkay 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 GullapalliYes, 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 Molis@Jamie, are you all set with this or do you still need help?

Jamie McKechnieThanks everyone for your answers, problem is resolved now. Steve MolisWhat ended up being the solution? M DEVHello 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