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.

  • Which object are you trying to create the formula on? Which fields? – Kyle Jun 4 '18 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 '18 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 '18 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 '18 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 '18 at 20:22
2

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 ) ) 
)

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.