Formula to calculate time between two dates, Salesforce example broken

Question

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?

Answer 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