2

I'm trying to create a formula that would calculate the business days between "Claim Filled/Submitted Date" and "Date/Time Closed" for claims turn time. I'm using the formula: 

(5 * ( FLOOR( ( date_1 - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( date_1 - DATE( 1900, 1, 8), 7 ) ) )
-
(5 * ( FLOOR( ( date_2 - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( date_2 - DATE( 1900, 1, 8), 7 ) ) )

However, I'm not sure how to replace date_1 and date_2 with the fields I want. I keep getting an error saying the custom field doesn't exist.

Improve this question
7
  • Which object are you trying to create the formula on? Which fields?
    – Kyle
    Jun 4, 2018 at 19:18
  • The fields are the "Claim Filled/Submitted Date" and "Date/Time Closed" for Cases. I'm creating the formula for a summary report since I don't have access to the Formula Editor.
    – Juan Fernandez
    Jun 4, 2018 at 19:31
  • 1
    Here's an example of what I've tried: (5 * ( FLOOR( ( ClosedDate - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( ClosedDate - DATE( 1900, 1, 8), 7 ) ) ) - (5 * ( FLOOR( ( Claim_Filed_Date__c - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( Claim_Filed_Date__c - DATE( 1900, 1, 8), 7 ) ) )
    – Juan Fernandez
    Jun 4, 2018 at 19:43
  • 2
    If you're using ClosedDate from the Case Object, that's a Date-Time field and you'll need to get the DateValue of it. That's likely why it's not working for you.
    – crmprogdev
    Jun 4, 2018 at 19:47
  • 1
    Even after trying to find the DateValue of it, the same error occurs: Field ClosedDate does not exist.
    – Juan Fernandez
    Jun 4, 2018 at 20:22

1 Answer 1

Reset to default
3

You mentioned in a comment that you are doing this as a formula for a summary report, instead of as a field on Case. Formulas in a summary report are for formulating a result based on aggregate values rather than per row. So for example you can use the Sum, Min, Max, or Average of a field value, instead of referencing the field itself.

If you want to do the formula you have entered then you will need to do it as a formula type field on the Case object. You should be able to use the formula you have referenced with some minor adjustments. Assuming that Claim_Filed_Date__c is a date field, this should work:

(5 * FLOOR( ( DATEVALUE(ClosedDate) - DATE( 1900, 1, 8) ) / 7 ) 
    + MIN( 5, MOD( DATEVALUE(ClosedDate) - DATE( 1900, 1, 8), 7 )  ) 
) - 
(5 * FLOOR( ( Claim_Filed_Date__c - DATE( 1900, 1, 8) ) / 7 ) 
      + MIN( 5, MOD( Claim_Filed_Date__c - DATE( 1900, 1, 8), 7 ) ) 
)
Improve this answer

You must log in to answer this question.

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