How to Calculate Number of Saturdays and Sundays Between Two Dates in Excel

Learn the Excel formula to count Saturdays and Sundays between two dates easily using SUMPRODUCT and WEEKDAY functions.

75 views

To calculate Saturdays and Sundays between two dates in Excel, use the following formula: `=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1 & ":" & A2)), 2) > 5))` Replace `A1` with the start date and `A2` with the end date. This formula counts the weekends within the specified date range.

FAQs & Answers

  1. How do I count only weekend days between two dates in Excel? Use the formula =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start_date & ":" & end_date)), 2) > 5)) replacing start_date and end_date with your cell references to count Saturdays and Sundays.
  2. Can I count only Saturdays or only Sundays between two dates in Excel? Yes, by adjusting the WEEKDAY function in the formula to check for specific days (e.g., 6 for Saturday or 7 for Sunday), you can count individual weekend days.
  3. What does the WEEKDAY function with type 2 mean in Excel? Using WEEKDAY with type 2 returns numbers 1 (Monday) through 7 (Sunday), allowing easy identification of weekends when the value is greater than 5.

Watch More

For more advanced date calculations in Excel, check out our tutorials on counting weekdays only, excluding holidays, and calculating working days between two dates using NETWORKDAYS. These tips help streamline your date management tasks.

  1. How to Calculate the Number of Sundays Between Dates in Excel Learn the Excel formula to count Sundays between two dates using SUMPRODUCT and WEEKDAY functions for accurate results.
  2. How to Calculate Weekends Between Dates in Excel Using Formulas Learn the Excel formula to calculate the number of weekends between two dates accurately, including how to exclude holidays.
  3. How to Calculate Weekends Between Dates in Excel Using Formulas Learn how to calculate the number of weekend days between two dates in Excel using simple formulas like NETWORKDAYS and DAYS.
  4. How to Calculate Mondays to Saturdays in Excel Using NETWORKDAYS Function Learn how to calculate Mondays to Saturdays in Excel with the NETWORKDAYS function, including excluding Sundays and holidays.
  5. How to Calculate Total Days in Excel Including Weekends Learn how to calculate the total number of days between dates in Excel, including weekends, using a simple subtraction formula.
  6. How to Identify Weekends in Excel Using Formulas Learn how to use Excel formulas like NETWORKDAYS and TEXT to identify weekends and workdays efficiently.
  7. How to Calculate Monday from Sunday Date in Excel Easily Learn how to calculate the Monday of a week from a given Sunday's date in Excel using simple formulas for accurate weekly date calculations.
  8. How to Calculate Days in Excel Excluding Saturdays and Sundays Using NETWORKDAYS Learn how to calculate working days in Excel excluding weekends and holidays using the NETWORKDAYS function.
  9. How to Calculate Days Excluding Sundays in Excel Using NETWORKDAYS.INTL Learn how to count days excluding Sundays in Excel with the NETWORKDAYS.INTL function for accurate weekday calculations.