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
)
