1

I'm new with salesforce formula and I have this requirement to compute the business hours with mins. in salesforce case.

what i did is that i set-up one field in case objects (data type = formula). and i applied the formula below. it's working however I cannot get accurate minutes value. i would like to ask for help regarding my formula. Thank you.

Case 1: I'm getting 2,088.00 but if via manual computation i should get 2088.25

Date/Time Opened LastModifiedDate
5/24/2021 4:45 PM 4/13/2022 5:10 PM

Case 2: I'm getting 0 as result but if via manual computation i should get 0.11

Date/Time Opened LastModifiedDate
11/22/2022 11:38 AM 11/22/2022 11:49 AM

Based from some articles salesforce is in GMT so what i did is i get the equivalent of 08:30 SGT to GMT which in this case 12:30 (if my understanding is correct)

Formula:

ROUND( 9 * (
( 5 * FLOOR( ( DATEVALUE( LastModifiedDate) - DATE( 1900, 1, 8) ) /
7) +
MIN(5,
MOD( DATEVALUE( LastModifiedDate ) - DATE( 1900, 1, 8), 7) +
MIN( 1, 24 / 8 * ( MOD( LastModifiedDate - DATETIMEVALUE( '1900-01-08 12:30:00' ), 1 ) ) )
)
)
-
( 5 * FLOOR( ( DATEVALUE( CreatedDate) - DATE( 1900, 1, 8) ) /
7) +
MIN( 5,
MOD( DATEVALUE( CreatedDate ) - DATE( 1996, 1, 1), 7 ) +
MIN( 1, 24 / 8 * ( MOD( CreatedDate - DATETIMEVALUE( '1900-01-08 12:30:00' ), 1) ) )
)
)
),2
)

0

You must log in to answer this question.

Browse other questions tagged .