Skip to main content
Topics#Data ManagementPregunta de Robert Chen
Robert Chen (Arcc Holdings Pte Ltd) ha preguntado en #Data Management
18 mar 2011, 10:56

Is there another way to do this date filter?

 I need to determine the number of days in each month that a contract is valid (with a given start date and end date) using the following formula. However by the time I get to total out all the fields i have exceeded the 5000 characters limit. Is there another way to do this?

 

If(if ( End_Date__c < DATE(2011,12,31), End_Date__c , datevalue("2011-12-31")) - if ( Start_Date__c > Date (2011, 12, 1), Start_Date__c, datevalue("2011-12-1"))<0,0,if ( End_Date__c < DATE(2011,12,31), End_Date__c , datevalue("2011-12-31")) - if ( Start_Date__c > Date (2011, 12, 1), Start_Date__c, datevalue("2011-12-1")-1))

 

The outcome of this formula is a number between 0 and 31. This particular formula is for the month of Dec, 2011. Then I use the same formula for each of the months.

 

Thanks for helping
2 respuestas
  1. Robert Chen (Arcc Holdings Pte Ltd)
    20 mar 2011, 5:10
    We have contracts that start and end on different days of the months. 

     

    We calculate commission based on number of days that the contract is still active

     

    Therefore for each contracts we need to determiine how many active days are there for each of the months.

     

    The formula does this for each month by changing the last date of each month in the formula.
Escribir una respuesta...
0/9000