 ShowAll Questionssorted byDate Posted Rakesh S

# How to calculate difference between two business dates and that is return in DateTime format

Hi All,

i want to calculate differece b/w two business days and expecting result is in DateTime format.

here i am getting hours. but i am expecting like MM/DD/YY HH:MM:SS.

Thanks. pkpnair
Rakesh,
https://developer.salesforce.com/docs/atlas.en-us.usefulFormulaFields.meta/usefulFormulaFields/formula_examples_dates.htm
-Prakash Anilkumar Kota

Hello Rakesh,

The formula for finding business hours between two Date/Time values expands on the formula for finding elapsed business days. It works on the same principle of using a reference Date/Time, in this case 1/8/1900 at 16:00 GMT (9 a.m. PDT), and then finding your Dates’ respective distances from that reference. The formula rounds the value it finds to the nearest hour and assumes an 8–hour, 9 a.m. – 5 p.m. work day.

eg:

ROUND( 8 * ( ( 5 * FLOOR( ( DATEVALUE( ***date/time_1*** ) - DATE( 1900, 1, 8) ) / 7) + MIN(5, MOD( DATEVALUE( date/time_1 ) - DATE( 1900, 1, 8), 7) + MIN( 1, 24 / 8 * ( MOD( date/time_1 - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1 ) ) ) ) ) - ( 5 * FLOOR( ( DATEVALUE( date/time_2 ) - DATE( 1900, 1, 8) ) / 7) + MIN( 5, MOD( DATEVALUE( date/time_2 ) - DATE( 1996, 1, 1), 7 ) + MIN( 1, 24 / 8 * ( MOD( date/time_2 - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1) ) ) ) ) ), 0 ) vikas rathi91