0

I am trying to calculate difference between to dates in formula field on start and end date. I am not getting accurate value.

I should calculate hours in weekdays that is Monday to Friday and 9 AM to 6 PM EST between given dates

below is my formula:

ROUND( 9 * (
( 5 * FLOOR( ( DATEVALUE( End_Date_Time__c ) - DATE( 1900, 1, 8) ) / 7) +
MIN(5,
MOD( DATEVALUE( End_Date_Time__c ) - DATE( 1900, 1, 8), 7) +
MIN( 1, 24 / 9 * ( MOD( End_Date_Time__c - DATETIMEVALUE( '1900-01-08 14:00:00' ), 1 ) ) )
)
)
-
( 5 * FLOOR( ( DATEVALUE( Start_Date_Time__c ) - DATE( 1900, 1, 8) ) / 7) +
MIN( 5,
MOD( DATEVALUE( Start_Date_Time__c ) - DATE( 1996, 1, 1), 7 ) +
MIN( 1, 24 / 9 * ( MOD( Start_Date_Time__c - DATETIMEVALUE( '1900-01-08 14:00:00' ), 1) ) )
)
)
),
0 )

In some scenarios I am getting expected results but in most not correct values.

James Z
  • 12,209
  • 10
  • 24
  • 44
Ram
  • 1
  • 1
  • Have you seen https://help.salesforce.com/s/articleView?id=000385836&type=1 and all caveats they listed? And https://stackoverflow.com/a/75002888/313628 – eyescream Jan 08 '23 at 08:10

0 Answers0