2

I'm aware the question "How do I calculate the time difference between two date/times?" has been asked a lot, and not just on here.

After doing the recommended thing and looking on this page (right at the bottom), I copied and pasted the formula to calculate the number of business hours between the time a case was opened and the time the case was closed.

However, despite being on the SF help guide, it doesn't work. I get an error saying:

Error: Incorrect parameter type for operator '-'. Expected Number, DateTime, received Date

Here is my formula

ROUND( 8 * (
( 5 * FLOOR( ( ClosedDate - DATE( 1900, 1, 8) ) / 7) +
MIN(5, 
MOD( ClosedDate - DATE( 1900, 1, 8), 7) +
MIN( 1, 24 / 8 * ( MOD( ClosedDate - DATETIMEVALUE( '1900-01-08 09:00: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 09:00:00' ), 1) ) )
)
) 
), 
0 )

Should I just change the DATE() function to DATETIME() or will that break it?

1

You are correct - Looking at the SFDC Object reference, Case.closedDate is a DateTime field so line 2 and line 4 will break

Looking at line 2 and line 4 - these are manipulating days, not seconds so I'd rewrite line 2 and line 4 as:

Line 2

( 5 * FLOOR( ( DATEVALUE(ClosedDate) - DATE( 1900, 1, 8) ) / 7) +

Line 4

MOD( DATEVALUE(ClosedDate) - DATE( 1900, 1, 8), 7) +

You should go to the doc and use the feedback form to point this out to SFDC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.