Print this page

Calculate business days between two date fields

Knowledge Article Number 000001100
Description

Formula fields give you the ability to calculate the number of days between two dates. However, that is not the same as the number of business days between the two dates. 

Resolution

The formula below determines the number of business days between the “Created Date” and “Date/Time Closed” standard fields in the Case object. You can modify this formula to calculate the number of business days between any date/time fields in any other object. Simply switch the date fields as appropriate.

 

Data type: Formula

Output type: Number (0 decimal places)

Formula

IF(IsClosed = True,
(DATEVALUE(ClosedDate) - DATEVALUE( CreatedDate )) -
( FLOOR ( ( ( (DATEVALUE(ClosedDate)) - DATEVALUE(CreatedDate)) / 7 ) ) * 2 ) +
CASE(MOD ( DATEVALUE(CreatedDate) - DATE( 1900,1,6 ),7),
0,CASE( MOD ((DATEVALUE(ClosedDate)) - DATE( 1900,1,6 ),7),0,1,2 ),
1,CASE( MOD ((DATEVALUE(ClosedDate)) - DATE( 1900,1,6 ),7),0,2,1 ),
IF(MOD(DATEVALUE(CreatedDate) - DATE( 1900,1,6 ),7) - MOD((DATEVALUE(ClosedDate)) - DATE( 1900,1,6 ),7) <= 0 ,0,
IF(MOD((DATEVALUE(ClosedDate)) - DATE( 1900,1,6 ),7) = 0,1,-2 ) ) )
,
((TODAY()) - DATEVALUE( CreatedDate )) -
( FLOOR ( ( ( (TODAY()) - DATEVALUE(CreatedDate)) / 7 ) ) * 2 ) +
CASE(MOD ( DATEVALUE(CreatedDate) - DATE( 1900,1,6 ),7),
0,CASE( MOD ((TODAY()) - DATE( 1900,1,6 ),7),0,1,2 ),
1,CASE( MOD ((TODAY()) - DATE( 1900,1,6 ),7),0,2,1 ),
IF(MOD(DATEVALUE(CreatedDate) - DATE( 1900,1,6 ),7) - MOD((TODAY()) - DATE( 1900,1,6 ),7) <= 0 ,0,
IF(MOD((TODAY()) - DATE( 1900,1,6 ),7) = 0,1,-2 ) ) )
)

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Example: If  your formula is only involving Date type fields

Amount of working days between two given dates, for a Custom Object can be obtained through the following steps:

If this solution is required for a Custom Object, then follow the Steps bellow:

  1. Click on Your Name and Setup / App Setup / Create / Object
  2. Click Edit next to the Custom Object's  Name
  3. Scroll down to the section "Custom Fields & Relationships" and click "New"
  4. Select Data Type: "Formula" and click "Next"
  5. Type Field Label "WorkingDays"
  6. Select Formula Return Type: "Number" and Decimal Places "0", then click "Next"
  7. Enter the Formula: 
    1. (ClosedDate - CreatedDate) - ( FLOOR ( ( ( (ClosedDate) - CreatedDate) / 7 ) ) * 2 ) + CASE(MOD ( CreatedDate - DATE( 1900,1,6 ),7), 0,CASE( MOD ((ClosedDate) - DATE( 1900,1,6 ),7),0,1,2 ), 1,CASE( MOD ((ClosedDate) - DATE( 1900,1,6 ),7),0,2,1 ), IF(MOD(CreatedDate - DATE( 1900,1,6 ),7) - MOD((ClosedDate) - DATE( 1900,1,6 ),7) <= 0 ,0, IF(MOD((ClosedDate) - DATE( 1900,1,6 ),7) = 0,1,-2 ) ) ) ​
  8. Click "Next" and continue to "Save"

Note: For this solution, we are assuming that the custom fields "CreatedDate" and "ClosedDate" have previously been created for the particular Custom Object.

If the objective is to calculate the business hours and exclude weekends the following formula can be used. This is 8am to 5pm PST (9 working hours)

ROUND( "NumberOfBusinessHoursWithinaDay" *( (5*FLOOR(( DATEVALUE( "YourEnd/RecentDateFieldHere" ) -DATE(1996,01,01))/7) 
+
MIN(5, 
MOD(DATEVALUE( "YourEnd/RecentDateFieldHere" )-DATE(1996,01,01), 7) +
MIN(1, 24/ "NumberOfBusinessHoursWithinaDay" *(MOD( "YourEnd/RecentDateFieldHere" -DATETIMEVALUE('1996-01-01 13:00:00'), 1)))
))

-

(5*FLOOR((DATEVALUE( "YourStart/OldDateFieldHere" )-DATE(1996,01,01))/7) +
MIN(5, 
MOD(DATEVALUE( "YourStart/OldDateFieldHere" )-DATE(1996,01,01), 7) +
MIN(1, 24/ "NumberOfBusinessHoursWithinaDay" *(MOD( "YourStart/OldDateFieldHere" -DATETIMEVALUE('1996-01-01 13:00:00'), 1)))
))
), 0) / "NumberOfBusinessHoursWithinaDay"


**Note if the business hours starts at a different time change DATETIMEVALUE('1996-01-01 13:00:00') appropriately, this is in GMT which is 8am PST.
**You may obtain negative values using the formula with some fields. If you want to avoid negative figures make sure that you're evaluating fields where one is ALWAYS later than the other.





promote demote