Apex Code Development You need to sign in to do that
Don't have an account?
Shawn Reichner 33 Rounding Issue on Formula field that calculates business hours between two dates
Hello,
I have the following formula and even though the formula field is set as a number type and has 2 decimals set, the below formula is still rounding up to a whole hour.
I can not figure out how to get this formula to show the actual hours between the two dates.
The example I have to share is Created date is 9/2/2020 at 4:56PM and the resolved Date which is the other date time field is set to 9/2/2020 5:09 PM so this should be a fraction of an hour, but the formula field shows 1 and it is throwing our numbers off.
Can anyone help? Please?
Thanks in advance,
Shawn
ROUND(8 * ( /*# of weeks * 5 + #days leftover (up to five) */ (5 * ( FLOOR( ( DATEVALUE(Resolved_Date__c)-DATE(1900,1,8) ) / 7 ) ) + MIN( 5, MOD( DATEVALUE(Resolved_Date__c)-DATE(1900,1,8), 7 ) ) )
(5 * ( FLOOR( ( DATEVALUE(CreatedDate)-DATE(1900,1,8) ) / 7 ) ) + MIN( 5, MOD( DATEVALUE(CreatedDate)-DATE(1900,1,8), 7 ) ) ) ) + /if outside business hours, read as first/last business hour (CST is GMT-6)/ IF( VALUE(MID(TEXT(Resolved_Date__c - 6/24),12,2)) > 17, 17, IF( VALUE(MID(TEXT(Resolved_Date__c - 6/24),12,2)) < 9, 9, VALUE(MID(TEXT(Resolved_Date__c - 6/24),12,2)) ))
IF( VALUE(MID(TEXT(CreatedDate - 6/24),12,2)) > 17, 17, IF( VALUE(MID(TEXT(CreatedDate - 6/24),12,2)) < 9, 9, VALUE(MID(TEXT(CreatedDate - 6/24),12,2)) )) , 2)
I have the following formula and even though the formula field is set as a number type and has 2 decimals set, the below formula is still rounding up to a whole hour.
I can not figure out how to get this formula to show the actual hours between the two dates.
The example I have to share is Created date is 9/2/2020 at 4:56PM and the resolved Date which is the other date time field is set to 9/2/2020 5:09 PM so this should be a fraction of an hour, but the formula field shows 1 and it is throwing our numbers off.
Can anyone help? Please?
Thanks in advance,
Shawn
ROUND(8 * ( /*# of weeks * 5 + #days leftover (up to five) */ (5 * ( FLOOR( ( DATEVALUE(Resolved_Date__c)-DATE(1900,1,8) ) / 7 ) ) + MIN( 5, MOD( DATEVALUE(Resolved_Date__c)-DATE(1900,1,8), 7 ) ) )
(5 * ( FLOOR( ( DATEVALUE(CreatedDate)-DATE(1900,1,8) ) / 7 ) ) + MIN( 5, MOD( DATEVALUE(CreatedDate)-DATE(1900,1,8), 7 ) ) ) ) + /if outside business hours, read as first/last business hour (CST is GMT-6)/ IF( VALUE(MID(TEXT(Resolved_Date__c - 6/24),12,2)) > 17, 17, IF( VALUE(MID(TEXT(Resolved_Date__c - 6/24),12,2)) < 9, 9, VALUE(MID(TEXT(Resolved_Date__c - 6/24),12,2)) ))
IF( VALUE(MID(TEXT(CreatedDate - 6/24),12,2)) > 17, 17, IF( VALUE(MID(TEXT(CreatedDate - 6/24),12,2)) < 9, 9, VALUE(MID(TEXT(CreatedDate - 6/24),12,2)) )) , 2)
Just in case anyone else is affected by the same issue, wanted to follow up that this was able to be resolved.
Thank you all again!