+ Start a Discussion
meli1.3950855704202322E12meli1.3950855704202322E12 

Calculate Days Open, excluding weekends

I have a field on Case, "Days Open", but want to exclude weekends from the resulting number.  I searched and found this from a previous post https://developer.salesforce.com/forums?id=906F00000008vlZIAQ,  but get a vague "syntax error" when I try to use this formula code to create my field - 
CASE(
MOD(DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7),
0, (TODAY() - DATEVALUE(CreatedDate)) - 1 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
1, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
2, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
3, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
4, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
5, (TODAY() - DATEVALUE(CreatedDate)) - 2 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
6, (TODAY() - DATEVALUE(CreatedDate)) - 2 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
null)

What's wrong with this code? 
Best Answer chosen by meli1.3950855704202322E12
Malni Chandrasekaran 2Malni Chandrasekaran 2
Meli1
This is really strange :)  There must be some special character in the middle which may be causing the error.

Please copy the below code. I recreated everything by typing and inserting the functions instead of copy / paste and I did not get that strange syntax error.
Note:I got that syntax error when I copy pasted the code :)

CASE(MOD(DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7),
0, (TODAY() - DATEVALUE(CreatedDate)) - 1 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
1, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
2, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
3, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
4, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
5, (TODAY() - DATEVALUE(CreatedDate)) - 2 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
6, (TODAY() - DATEVALUE(CreatedDate)) - 2 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
null)

Please mark this as solved if this solves your problem

All Answers

Malni Chandrasekaran 2Malni Chandrasekaran 2
Meli1
This is really strange :)  There must be some special character in the middle which may be causing the error.

Please copy the below code. I recreated everything by typing and inserting the functions instead of copy / paste and I did not get that strange syntax error.
Note:I got that syntax error when I copy pasted the code :)

CASE(MOD(DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7),
0, (TODAY() - DATEVALUE(CreatedDate)) - 1 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
1, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
2, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
3, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
4, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
5, (TODAY() - DATEVALUE(CreatedDate)) - 2 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
6, (TODAY() - DATEVALUE(CreatedDate)) - 2 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
null)

Please mark this as solved if this solves your problem
This was selected as the best answer
meli1.3950855704202322E12meli1.3950855704202322E12
No error!!! I have been working for hours on this. Thank you so very much for replying! Now I can enjoy my weekend!
Malni Chandrasekaran 2Malni Chandrasekaran 2
Please mark it as solved :)
Malni Chandrasekaran 2Malni Chandrasekaran 2
Have a great Weekend !