답변 1개
You can use following formula to calculate weekdays and then use this formula field as filter in report
Purpose:
Calculate the number of days between two dates while excluding weekends or excluding weekdays.
Formulas provided:- Weekday Count Formula
- Weekend Days Count Formula
- Create a formula field that returns a number.
- Paste in one of the two formulas.
- Replace StartDate__c and EndDate__c with your custom field values.
- If using Date/Time fields
- Replace with DATEVALUE(YourCustomDateTime__c) instead.
- Assumptions/Limitations/Background:
- Weekdays are defined as Mon-Fri and weekends as Sat & Sun
- Holidays are not addressed
- June 6, 1985 is a long distant Monday used as a reference point
- The result will include BOTH the START and END dates.
- Mon-Sun is counted as 5 Weekdays and 2 Weekend Days.
- Mon-Fri is NOT (Fri subtract Mon) = 4 Elapsed Days.
- Sat-Sun is NOT (Sun subtract Sat) = 1 Elapsed Day.
- If you use another Formula field as the start or end dates you MAY hit a compilation limit.
- Workaround - Use workflow rules to save the output of the formula fields into a regular date field.
- Be sure to TEST the formulas FIRST.
Weekday Count Formula:
CASE(MOD( StartDate__c - DATE(1985,6,24),7),
0 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE( MOD( EndDate__c - StartDate__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE( MOD( EndDate__c - StartDate__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE( MOD( EndDate__c - StartDate__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+
(FLOOR(( EndDate__c - StartDate__c )/7)*5)
Weekend Days Count Formula:
CASE(MOD( StartDate__c - DATE(1985,6,24),7),
0 , CASE( MOD( EndDate__c - StartDate__c, 7),1,0,2,0,3,0,4,0,5,1,6,2,0),
1 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,0,3,0,4,0,5,2,2),
2 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,0,3,1,2),
3 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,1,2),
4 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,1,2),
5 , CASE( MOD( EndDate__c - StartDate__c, 7),0,1,2),
6 , CASE( MOD( EndDate__c - StartDate__c, 7),6,2,1),
999)
+
(FLOOR(( EndDate__c - StartDate__c )/7)*2)
