'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Calculate Alternate Working Saturdays in Excel
Question details
Calculate the total number of alternate working Saturdays or generate a list of alternate Saturday dates between a specific start date and end date.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- An employee is scheduled to work on alternating Saturdays (e.g., from May 2, 2024, through June 30, 2024), and the user needs an automated way to count or list these specific working days.
- Observed behavior
- The user requires a formula to correctly isolate and count every second Saturday between two dates, or to project the specific calendar dates for those working Saturdays.
Ensure you have your specific start date and end date entered into separate cells in your spreadsheet, and verify that both cells are correctly formatted as Dates rather than plain text.
Count Alternate Saturdays Using NETWORKDAYS.INTL
Use a combination of the NETWORKDAYS.INTL and ROUNDUP functions to calculate the exact number of alternating Saturdays between two dates.
The NETWORKDAYS.INTL function allows you to define custom weekends using a 7-digit string (Monday to Sunday) where '1' is a non-working day and '0' is a working day. By dividing the total number of Saturdays by 2 and rounding up, you get the count of alternating Saturdays.
Enter your start date (e.g., May 2, 2024) in cell A2 and your end date (e.g., June 30, 2024) in cell B2.
Select a blank cell where you want the count to appear and type the formula: =ROUNDUP(NETWORKDAYS.INTL(A2,B2,"1111101")/2,0)
Press the Enter key. The cell will now display the total number of alternate working Saturdays during that period.
Generate a List of Alternate Saturdays (Modern Excel)
If you need the actual calendar dates of the alternate working Saturdays, use the SEQUENCE function in modern spreadsheet versions.
List Alternate Saturdays Using the Manual Indexing Method
For older versions of Excel where the SEQUENCE function is unavailable, use manual intervals to calculate the date of every other Saturday.
Easily Calculate Complex Work Schedules with WPS Office
WPS Spreadsheet fully supports advanced date and time functions, including NETWORKDAYS.INTL and dynamic arrays like SEQUENCE. It is perfectly equipped to handle complex payroll, attendance, and custom working schedules with complete accuracy.
- 1. Open WPS Spreadsheet: Launch WPS Office and click on 'Spreadsheet' to open your work schedule document.
- 2. Insert Schedule Dates: Input the employee's joining date in one cell and the end of the calculation period in another.
- 3. Apply Date Formulas: Click on the 'Formulas' tab, select 'Date & Time', and use NETWORKDAYS.INTL to instantly calculate alternate weekends.
- 4. Format Automatically: Use WPS Spreadsheet's smart formatting tools to display results cleanly as either total days or specific calendar dates.
Frequently Asked Questions
How do I calculate total working days including regular weekdays and alternate Saturdays?
You can calculate this by combining two formulas. First, calculate the standard Monday-to-Friday working days using =NETWORKDAYS(start_date, end_date). Then, calculate the alternate Saturdays using =ROUNDUP(NETWORKDAYS.INTL(start_date, end_date, "1111101")/2, 0). Add both results together to get the total working days.
What does the '1111101' mean in the NETWORKDAYS.INTL formula?
In NETWORKDAYS.INTL, custom weekends are defined by a 7-character text string representing Monday through Sunday. A '1' indicates a non-working day, while a '0' indicates a working day. The string '1111101' marks Monday-Friday and Sunday as non-working days, leaving only Saturday as the active working day.
Why is my date formula returning a five-digit number like 45414?
Spreadsheet software stores dates as sequential serial numbers for calculation purposes. If you see a large number instead of a date, simply right-click the cell, select 'Format Cells', and change the category from 'General' or 'Number' to 'Date'.
