+ Start a Discussion

Need help with Date Formula Count Number of Days - Date Formula in Roll-up Summary Field



Is it possible to count the number of records there are in a related list, only if their start dates is greater than the master objects start date? 


We have a School_Calendar__c which is the child of the Account.  The School Calendar has start and end dates and contains child object Calendar_Events__c, which are a list of events where the school has closings, these records also have start and end dates, example, Spring Break, the school closes between x date and x date.


The Order__c is another child object to the Account and includes a start date.


I'm trying to Count the number of Calendar_Event_Days__c there are, but only if the start date of the Calendar Event is greater than the start date of the Order.


Thanks in advance!


Bhawani SharmaBhawani Sharma

1.Create a formula field(Number) in the child object.

if(StartDate > Parent.StartDate,1,0)


So all the child records having the start date greater than parent will be with the value 1.


2.Create a Roll Up summary field on the parent to get the sum of the children formula field:

SUM(child formula field name);


SO this field will be with the counter how many children are there having startdate greter.


Thanks Tech Force,


My issue is that the Order__c is not related to the Calendar Events__c, but I need to compare the start dates between these two objects. They have the Account Name in common.


The Order contains a lookup field to the Account, and the Account has the School_Calendar__c as a child object.  Then the School_Calendar__c has the child object Calendar_Events__c, which lists all the days off. 


I like the way you went about it, but I need to figure out how to count the days of Calendar_Events__c if the start date is > the Order__c start date. 


Would I have to develop a controller to do this? 


I actually just tried to put a lookup field on the School_Calendar__c to lookup the Order__c.  I applied the Number formula kind of like you suggested, but said "If the Order__c start date is > the Calendar_Event__c start date, then count the days between the start and end date.  Otherwise, don't count it.  The formula worked fine and would total the amount of days the school had off for the particlar event.  But for some reason, I couldnt do a roll-up summary on the School Calendar__c to SUM  the total Days off from that calculation...weird.  I was able to sum a similar formula field, but I guess since this formula references another object, it won't let me SUM it.

Bhawani SharmaBhawani Sharma

Roll up summary fields are only allowed with the master detail relationship.