0

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)
Improve this question
4
  • Why do you have double asterisks on the last line of the formula?
    – David Cheng
    Commented Sep 3, 2020 at 19:47
  • I am not sure why that is there, the paste of the code must have done it, as that is not in my code in salesforce. Good eye, any insights on the issue at hand?
    – Shawn
    Commented Sep 3, 2020 at 20:05
  • At first glance it looks like each component of your formula returns an integer. Please edit your post to call out which component(s) might return a fractional value and give an example.
    – David Cheng
    Commented Sep 3, 2020 at 21:40
  • Thank you again David, and not sure what else to provide sir. The formula field formula is above, and the example is also listed in the description of the post. We have created date which of course is a standard salesforce field and the resolved date field which both are stated in the formula above. If created date is 9/2/2020 4:56PM and resolved Date is 9/2/2020 5:09PM is should be the equivalent of 13 minutes or that fraction of an hour like 0.22 but the formula field using the formula above is showing 1 hour, so it is rounding up. Does that help?
    – Shawn
    Commented Sep 4, 2020 at 12:10

1 Answer 1

Reset to default
1

Thank you everyone who viewed! Figured this out right now, I can not believe I was this blind, but it seems that from the default date 1900,1,8 above I was missing the time element to make the hours come into play. By adding 16:00:00 to each instance of this, I got my correct rounding needed! 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 for viewing, and for David for attempting to help out, you guys rock!

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .