To count the number of Saturdays between two dates, use the following formula:
Code:
=INT((B1-A1)/7)*2+IF(WEEKDAY(A1)<=7-MOD(B1-A1,7),1,0)+IF(WEEKDAY(B1)=MOD(B1-A1,7)+1,1,0)
Here's how it works:
- INT((B1-A1)/7)*2 calculates the number of full weeks between the two dates and multiplies it by 2 to get the number of Saturdays in those weeks.
- IF(WEEKDAY(A1)<=7-MOD(B1-A1,7),1,0) adds 1 if the start date is on a Saturday or a day before the last Saturday before the end date.
- IF(WEEKDAY(B1)=MOD(B1-A1,7)+1,1,0) adds 1 if the end date is on a Saturday or a day after the first Saturday after the start date.
To add this to your existing formula, you can create a new column and use the above formula to calculate the number of Saturdays for each employee. Then, you can add the two results (number of working days and number of Saturdays) to get the total number of days of leave taken.