0

I am looking to count business days from when a status is picked in a pick list on a case. I have the field awaiting customer input data adding today's date when the status is picked. This is working what I can't figure out is how to count the number of business days from the start date. I tried this code that I found but I am getting a Syntax Error. Any help would be great.

CASE(
MOD(DATE(Awaiting_Customer_Input_Date__c) - DATE(1900, 1, 7), 7),
0, (TODAY() - DATE(Awaiting_Customer_Input_Date__c)) - 1 - FLOOR((TODAY() - DATE(Awaiting_Customer_Input_Date__c))/7)*2,
1, (TODAY() - DATE(Awaiting_Customer_Input_Date__c)) - FLOOR((TODAY() - DATE(Awaiting_Customer_Input_Date__c))/7)*2,
2, (TODAY() - DATE(Awaiting_Customer_Input_Date__c)) - FLOOR((TODAY() - DATE(Awaiting_Customer_Input_Date__c))/7)*2,
3, (TODAY() - DATE(Awaiting_Customer_Input_Date__c)) - FLOOR((TODAY() - DATE(Awaiting_Customer_Input_Date__c))/7)*2,
4, (TODAY() - DATE(Awaiting_Customer_Input_Date__c)) - FLOOR((TODAY() - DATE(Awaiting_Customer_Input_Date__c))/7)*2,
5, (TODAY() - DATE(Awaiting_Customer_Input_Date__c)) - 2 - FLOOR((TODAY() - DATE(Awaiting_Customer_Input_Date__c))/7)*2,
6, (TODAY() - DATE(Awaiting_Customer_Input_Date__c)) - 2 - FLOOR((TODAY() - DATE(Awaiting_Customer_Input_Date__c))/7)*2,
null)
2
  • You didn't MOD any of the other values inside parentheses... What is the type of the Awaiting_Customer_Input_Date__c field? If it's already a Date field you can just remove the DATE(...) calls and replace with .... – Adrian Larson Jun 14 '17 at 1:30
  • Yes its just a Date field – Ericv Jun 14 '17 at 1:55
0

Copying your formula into my org and tweaking just the API Names, I was at least able to compile the formula just by changing DATE(DateField__c) to DateField__c everywhere you use the former.

Instead of:

DATE(DateField__c)

Just use:

DateField__c

Note that you do have one appropriate usage of the DATE function which you should leave untouched:

DATE(1900, 1, 7)
3
  • CASE( MOD(DATE(Awaiting_Customer_Input_Date__c) - DATE(1900, 1, 7), 7), 0, (TODAY() - Awaiting_Customer_Input_Date__c - 1 - FLOOR((TODAY() - Awaiting_Customer_Input_Date__c/7)*2, 1, (TODAY() - Awaiting_Customer_Input_Date__c - FLOOR((TODAY() - Awaiting_Customer_Input_Date__c/7)*2, 2, (TODAY() - Awaiting_Customer_Input_Date__c - FLOOR((TODAY() - Awaiting_Customer_Input_Date__c/7)*2, 3, (TODAY() - Awaiting_Customer_Input_Date__c - FLOOR((TODAY() - Awaiting_Customer_Input_Date__c/7)*2, 4, (TODAY() - Awaiting_Customer_Input_Date__c - FLOOR((TODAY() - Awaiting_Customer_Input_Date__c/7)*2, – Ericv Jun 14 '17 at 2:12
  • That doesn't seem to be working. – Ericv Jun 14 '17 at 2:14
  • You missed the first one. – Adrian Larson Jun 14 '17 at 2:23
0

I tried by removing with what @adrian said. Its working. I have created a formula field with number. You can try the below

CASE(
    MOD((Awaiting_Customer_Input_Date__c) - DATE(1900, 1, 7), 7),
    0, (TODAY() - (Awaiting_Customer_Input_Date__c)) - 1 - FLOOR((TODAY() - (Awaiting_Customer_Input_Date__c))/7)*2,
    1, (TODAY() - (Awaiting_Customer_Input_Date__c)) - FLOOR((TODAY() - (Awaiting_Customer_Input_Date__c))/7)*2,
    2, (TODAY() - (Awaiting_Customer_Input_Date__c)) - FLOOR((TODAY() - (Awaiting_Customer_Input_Date__c))/7)*2,
    3, (TODAY() - (Awaiting_Customer_Input_Date__c)) - FLOOR((TODAY() - (Awaiting_Customer_Input_Date__c))/7)*2,
    4, (TODAY() - (Awaiting_Customer_Input_Date__c)) - FLOOR((TODAY() - (Awaiting_Customer_Input_Date__c))/7)*2,
    5, (TODAY() - (Awaiting_Customer_Input_Date__c)) - 2 - FLOOR((TODAY() - (Awaiting_Customer_Input_Date__c))/7)*2,
    6, (TODAY() - (Awaiting_Customer_Input_Date__c)) - 2 - FLOOR((TODAY() - (Awaiting_Customer_Input_Date__c))/7)*2,
    null)
2
  • So basically this is a duplicate answer. – Adrian Larson Jun 14 '17 at 10:37
  • Thank you bharath, that seemed to do the trick with the errors. I am testing it now. Thanks again everyone for all your help. – Ericv Jun 14 '17 at 18:31

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.