Error: Incorrect parameter type for function 'MOD()'. Expected Number, received Date/Time

Question

I need calculate only workdays for DUE DATE field which uses this existing formula -

IF(
    ISNULL(DATEVALUE(DATETIMEVALUE(NewCaseDate__c))), 
    DATEVALUE(CreatedDate), 
    DATEVALUE(DATETIMEVALUE(NewCaseDate__c))
) + Days_To_Respond__c"

I have found a formula like this but it throws error

Error: Incorrect parameter type for function 'MOD()'. Expected Number, received Date/Time

Also, formula return type will be DATE and we can't change it.

DATEVALUE(CreatedDate) + 
FLOOR(
    (Days_To_Respond__c - 
    IF(
        MOD(CreatedDate, 7) = 5, 
        2, 
        IF(
            MOD(CreatedDate, 7) = 0, 
            1, 
            0
        )
    )
    ) / 5
) * 7 + 
MIN(
    Days_To_Respond__c - 
    IF(
        MOD(CreatedDate, 7) = 5, 
        2, 
        IF(
            MOD(CreatedDate, 7) = 0, 
            1, 
            0
        )
    ), 
    5
)

Answer 1

MOD(CreatedDate, 7) doesn't work, because you can't divide a date by a number. However, just choose a date that is a Sunday, and subtract that from the CreatedDate.

MOD(CreatedDate - DATE(1970, 1, 4), 7)

Answer 2

The issue is MOD() function expects a number, but you're providing a date/time value.

You can try this formula:

DATE(
        YEAR(CreatedDate),
        MONTH(CreatedDate),
        DAY(CreatedDate) +
        FLOOR((Days_To_Respond__c + 
                CASE(MOD(DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7),
                        0, 1,
                        6, 2,
                        0
                )
        ) / 5) * 7 +
        MIN(
                MOD(Days_To_Respond__c + 
                        CASE(MOD(DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7),
                                0, 1,
                                6, 2,
                                0
                        ), 5),
                5
        )
)

This DATE(YEAR(CreatedDate), MONTH(CreatedDate), DAY(CreatedDate)) part of the formula calculates the base date.

This FLOOR((Days_To_Respond__c + CASE(...)) / 5) * 7 part of the formula adds the number of workdays.

This MIN(MOD(Days_To_Respond__c + CASE(...), 5), 5) part of the formula adjusts for weekends.

---

Updated:

I have updated the formula as per your comment to consider the NewCaseDate__c field when it is populated.

DATE(
        YEAR(IF(ISBLANK(NewCaseDate__c), CreatedDate, NewCaseDate__c)),
        MONTH(IF(ISBLANK(NewCaseDate__c), CreatedDate, NewCaseDate__c)),
        DAY(IF(ISBLANK(NewCaseDate__c), CreatedDate, NewCaseDate__c)) +
        FLOOR((Days_To_Respond__c +  
                CASE(MOD(DATEVALUE(IF(ISBLANK(NewCaseDate__c), CreatedDate, NewCaseDate__c)) - DATE(1900, 1, 7), 7),
                        0, 1,
                        6, 2,
                        0
                )
        ) / 5) * 7 + 
        MIN(
                MOD(Days_To_Respond__c + 
                        CASE(MOD(DATEVALUE(IF(ISBLANK(NewCaseDate__c), CreatedDate, NewCaseDate__c)) - DATE(1900, 1, 7), 7),
                                0, 1,
                                6, 2,
                                0
                        ), 5),
                5
        )
)

This formula checks if NewCaseDate__c is populated. If it is, it uses NewCaseDate__c as the base date. otherwise, it uses CreatedDate. The rest of the formula remains the same, only workdays are counted when adding Days_To_Respond__c.

Answer 3

try something like this

public static Date calculateDueDate(Date startDate, Integer daysToRespond) {
Integer daysToAdd = 0;
while(daysToAdd < daysToRespond) {
    startDate = startDate.addDays(1);
    if(!(startDate.format('E').equalsIgnoreCase('Sat') || startDate.format('E').equalsIgnoreCase('Sun'))) {
        daysToAdd++;
    }
}
return startDate;

}