I have a formula field that should be calculating the number of weekdays a case was open. If the case is closed it calculates weekdays between create and closed date; if case is open it calculates age from today and create date. However, I have a closed case that was opened Thursday 8/15/2019 and closed Monday 8/19/2019 and the number that is being returned is zero. Can anyone see my error?
 

If( IsClosed,

(CASE(MOD(Datevalue(CreatedDate) - DATE(1985,6,24),7),
0 , CASE(MOD(ClosedDate - CreatedDate ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE(MOD(ClosedDate - CreatedDate ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE(MOD(ClosedDate - CreatedDate ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE(MOD(ClosedDate - CreatedDate ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE(MOD(ClosedDate - CreatedDate ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE(MOD(ClosedDate - CreatedDate ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE(MOD(ClosedDate - CreatedDate ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+ (FLOOR((ClosedDate - CreatedDate)/7)*5)
-
IF(OR(
NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=0),
NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=1)), 1, 0)),

(CASE(MOD(Datevalue(CreatedDate) - DATE(1985,6,24),7),
0 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+ (FLOOR((Today() - Datevalue(CreatedDate))/7)*5)
-
IF(OR(
NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=0),
NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=1)), 1, 0)))

 

I have a formula field that I can't get to work properly. I want it to calculate the days between open and closed but subtract the time frame if it was ever in Hold status. When I dont have day

If( IsClosed,

(CASE(MOD(Datevalue(CreatedDate) - DATE(1985,6,24),7),
0 , CASE(MOD(((ClosedDate-CreatedDate)-( L_Hold_Status__c - E_Hold_Status__c )) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE(MOD(((ClosedDate-CreatedDate)-( L_Hold_Status__c - E_Hold_Status__c )) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE(MOD(((ClosedDate-CreatedDate)-( L_Hold_Status__c - E_Hold_Status__c )) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE(MOD(((ClosedDate-CreatedDate)-( L_Hold_Status__c - E_Hold_Status__c )) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE(MOD(((ClosedDate-CreatedDate)-( L_Hold_Status__c - E_Hold_Status__c )) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE(MOD(((ClosedDate-CreatedDate)-( L_Hold_Status__c - E_Hold_Status__c )) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE(MOD(((ClosedDate-CreatedDate)-( L_Hold_Status__c - E_Hold_Status__c )) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+ (FLOOR((ClosedDate-CreatedDate)/7)*5)
-
IF(OR(
NOT(MOD( Datevalue(CreatedDate-(L_Hold_Status__c -E_Hold_Status__c)) - DATE (2000 ,1, 1) ,7)=0),
NOT(MOD( Datevalue(CreatedDate-(L_Hold_Status__c -E_Hold_Status__c)) - DATE (2000 ,1, 1) ,7)=1)), 1, 0)),

(CASE(MOD(Datevalue(CreatedDate) - DATE(1985,6,24),7),
0 , CASE(MOD(Today() - Datevalue(CreatedDate-(L_Hold_Status__c -E_Hold_Status__c)) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE(MOD(Today() - Datevalue(CreatedDate-(L_Hold_Status__c -E_Hold_Status__c)) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE(MOD(Today() - Datevalue(CreatedDate-(L_Hold_Status__c -E_Hold_Status__c)) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE(MOD(Today() - Datevalue(CreatedDate-(L_Hold_Status__c -E_Hold_Status__c)) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE(MOD(Today() - Datevalue(CreatedDate-(L_Hold_Status__c -E_Hold_Status__c)) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE(MOD(Today() - Datevalue(CreatedDate-(L_Hold_Status__c -E_Hold_Status__c)) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE(MOD(Today() - Datevalue(CreatedDate-(L_Hold_Status__c -E_Hold_Status__c)) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+ (FLOOR((Today() - Datevalue(CreatedDate-(L_Hold_Status__c - E_Hold_Status__c)))/7)*5)
-
IF(OR(
NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=0),
NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=1)), 1, 0)))

s in the hold status fields it calculates accurately. When I add dates to those fields it only subtracts one day. Any help would be greatly appreciated!

AND (
  OR (
    [Case].RecordType.Name = 'Community',
    [Case].RecordType.Name = 'Payer',
    [Case].RecordType.Name = 'Partner'
  ),
  OR (
    AND (
      ISCHANGED([Case].Send_Attachment_Notification__c ),
      [Case].Send_Attachment_Notification__c = true
    ),
    AND (
      ISCHANGED([Case].Send_Comment_Notification__c ),
      [Case].Send_Comment_Notification__c = true
    )
  ),
  BEGINS([Case].OwnerId, '005'),
)

global class CreateLeadEmailService implements Messaging.InboundEmailHandler {

    global Messaging.InboundEmailResult handleInboundEmail(Messaging.InboundEmail email, Messaging.InboundEnvelope envelope){
        Messaging.InboundEmailResult result = new Messaging.InboundEmailresult();
        String leadLastName=email.plaintextbody.split('Last Name: ').get(1).split('Company Name').get(0);
        String leadFirstName=email.plaintextbody.split('First Name: ').get(1).split('Last Name').get(0); 
        String leadCompany= email.plaintextbody.split('Company Name: ').get(1).split('Phone').get(0);
        String leadPhone= email.plaintextbody.split('Phone: ').get(1).split('Email:').get(0);
        String leadEmail=email.plaintextbody.split('Email: ').get(1).split('<mailto').get(0);
        String leadState=email.plaintextbody.split('Region: ').get(1).split('Size of').get(0);        
        String leadProduct=email.plaintextbody.split('Product: ').get(1).split('Plan').get(0);
        String leadPlan=email.plaintextbody.split('Plan: ').get(1).split('Site:').get(0);  
        String leadDes=email.plaintextbody;
        String leadSub=email.subject;
        String leadCustomerSize=email.plaintextbody.split('Base: ').get(1).split('\\.00').get(0).replaceAll(',','');
         Integer leadSize= Integer.valueOf(email.plaintextbody.split('Base: ').get(1).split('\\.00').get(0).replaceAll(',',''));
        Lead l=new Lead(LastName=leadLastName, FirstName=leadFirstname, Email=leadEmail, Subject_del__c=leadSub, Description=leadDes, Company=leadCompany, 
                        Phone=leadPhone, Availity_Market_Region__c=leadState, Plan__c=leadPlan, Product__c=leadProduct, Size_of_Customer_Base__c=leadSize );
        
        insert l;
 
        if(email.textAttachments != null)
        {
            // Save attachments, if any
            for (Messaging.Inboundemail.TextAttachment tAttachment : email.textAttachments) {
              Attachment attachment = new Attachment();
             
              attachment.Name = tAttachment.fileName;
              attachment.Body = Blob.valueOf(tAttachment.body);
              attachment.ParentId = l.Id;
              insert attachment;
            }
        }
        if(email.binaryAttachments != null)
        {
            for (Messaging.Inboundemail.BinaryAttachment bAttachment : email.binaryAttachments) {
              Attachment attachment = new Attachment();
             
              attachment.Name = bAttachment.fileName;
              attachment.Body = bAttachment.body;
              attachment.ParentId = l.Id;
              insert attachment;
            }
        }
      
        
        return result;
    }

global class CreateLeadEmailService implements Messaging.InboundEmailHandler {

    global Messaging.InboundEmailResult handleInboundEmail(Messaging.InboundEmail email, Messaging.InboundEnvelope envelope){
        Messaging.InboundEmailResult result = new Messaging.InboundEmailresult();
        String leadName=email.fromName;
        String leadDes=email.plaintextbody;
        String leadSub=email.subject;
        String leadEmail=email.fromAddress;
        Lead l=new Lead(LastName=leadName, Email=leadEmail, Subject_del__c=leadSub, Description=leadDes, Company=leadEmail.split('@').get(1).split('\\.').get(0) );
        insert l;
        return result;
    }
}

I have a formula field that should be calculating the number of weekdays a case was open. If the case is closed it calculates weekdays between create and closed date; if case is open it calculates age from today and create date. However, I have a closed case that was opened Thursday 8/15/2019 and closed Monday 8/19/2019 and the number that is being returned is zero. Can anyone see my error?
 

If( IsClosed,

(CASE(MOD(Datevalue(CreatedDate) - DATE(1985,6,24),7),
0 , CASE(MOD(ClosedDate - CreatedDate ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE(MOD(ClosedDate - CreatedDate ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE(MOD(ClosedDate - CreatedDate ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE(MOD(ClosedDate - CreatedDate ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE(MOD(ClosedDate - CreatedDate ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE(MOD(ClosedDate - CreatedDate ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE(MOD(ClosedDate - CreatedDate ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+ (FLOOR((ClosedDate - CreatedDate)/7)*5)
-
IF(OR(
NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=0),
NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=1)), 1, 0)),

(CASE(MOD(Datevalue(CreatedDate) - DATE(1985,6,24),7),
0 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+ (FLOOR((Today() - Datevalue(CreatedDate))/7)*5)
-
IF(OR(
NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=0),
NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=1)), 1, 0)))

 

global class CreateLeadEmailService implements Messaging.InboundEmailHandler {

    global Messaging.InboundEmailResult handleInboundEmail(Messaging.InboundEmail email, Messaging.InboundEnvelope envelope){
        Messaging.InboundEmailResult result = new Messaging.InboundEmailresult();
        String leadLastName=email.plaintextbody.split('Last Name: ').get(1).split('Company Name').get(0);
        String leadFirstName=email.plaintextbody.split('First Name: ').get(1).split('Last Name').get(0); 
        String leadCompany= email.plaintextbody.split('Company Name: ').get(1).split('Phone').get(0);
        String leadPhone= email.plaintextbody.split('Phone: ').get(1).split('Email:').get(0);
        String leadEmail=email.plaintextbody.split('Email: ').get(1).split('<mailto').get(0);
        String leadState=email.plaintextbody.split('Region: ').get(1).split('Size of').get(0);        
        String leadProduct=email.plaintextbody.split('Product: ').get(1).split('Plan').get(0);
        String leadPlan=email.plaintextbody.split('Plan: ').get(1).split('Site:').get(0);  
        String leadDes=email.plaintextbody;
        String leadSub=email.subject;
        String leadCustomerSize=email.plaintextbody.split('Base: ').get(1).split('\\.00').get(0).replaceAll(',','');
         Integer leadSize= Integer.valueOf(email.plaintextbody.split('Base: ').get(1).split('\\.00').get(0).replaceAll(',',''));
        Lead l=new Lead(LastName=leadLastName, FirstName=leadFirstname, Email=leadEmail, Subject_del__c=leadSub, Description=leadDes, Company=leadCompany, 
                        Phone=leadPhone, Availity_Market_Region__c=leadState, Plan__c=leadPlan, Product__c=leadProduct, Size_of_Customer_Base__c=leadSize );
        
        insert l;
 
        if(email.textAttachments != null)
        {
            // Save attachments, if any
            for (Messaging.Inboundemail.TextAttachment tAttachment : email.textAttachments) {
              Attachment attachment = new Attachment();
             
              attachment.Name = tAttachment.fileName;
              attachment.Body = Blob.valueOf(tAttachment.body);
              attachment.ParentId = l.Id;
              insert attachment;
            }
        }
        if(email.binaryAttachments != null)
        {
            for (Messaging.Inboundemail.BinaryAttachment bAttachment : email.binaryAttachments) {
              Attachment attachment = new Attachment();
             
              attachment.Name = bAttachment.fileName;
              attachment.Body = bAttachment.body;
              attachment.ParentId = l.Id;
              insert attachment;
            }
        }
      
        
        return result;
    }

global class CreateLeadEmailService implements Messaging.InboundEmailHandler {

    global Messaging.InboundEmailResult handleInboundEmail(Messaging.InboundEmail email, Messaging.InboundEnvelope envelope){
        Messaging.InboundEmailResult result = new Messaging.InboundEmailresult();
        String leadName=email.fromName;
        String leadDes=email.plaintextbody;
        String leadSub=email.subject;
        String leadEmail=email.fromAddress;
        Lead l=new Lead(LastName=leadName, Email=leadEmail, Subject_del__c=leadSub, Description=leadDes, Company=leadEmail.split('@').get(1).split('\\.').get(0) );
        insert l;
        return result;
    }
}

I have performed a fast lookup and retrieved a list of objects. I need to make a decision based on the number of records returned - can I do this with a formula instead of having to use a loop to count the records?

Hi,

I need to create one formula field (returns number) which will calculate Case Age (In days) such that it will keep incrementing until case is closed. If the case is closed it will stop counting. This excludes weekends.

Can someone please help me on this?

Thanks,
Pooja

I need to export a report as a CSV to an FTP site or email it on a daily or weekly basis.

 

I have a Report.  I need to get the report out of Salesforce somehow into CSV format.

 

Ideally, this would be scheduled.

I'm a developer so I can do developer stuff.  However, I don't know APEX and I don't know VisualForce.  I can get someone to do that stuff if we need to.

Thanks!