'%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 Add 30 Workdays and Find the Next Payout Date in Excel
Question details
The user needs to calculate an eligibility date by adding 30 working days to a hire date, and then automatically find the exact or next scheduled payout date from a predefined list.
- Product
- Excel and WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Calculating employee benefits eligibility or determining HR payroll schedules based on working days.
- Observed behavior
- Requires a formula combination that correctly skips weekends (and holidays) to find the 30th workday, then references an ascending list of payroll dates to return the next valid payout schedule.
Ensure your list of scheduled payout dates is sorted in ascending order (from earliest to latest). If you need to exclude holidays from the 30-day calculation, prepare a separate column listing your company's recognized holiday dates.
Use a Nested WORKDAY and XLOOKUP Formula
Combine the WORKDAY function to find the 30th working day and the XLOOKUP function to match that date against your payout schedule.
The WORKDAY function calculates a future date by adding a specified number of working days, automatically skipping weekends. By wrapping this in XLOOKUP with a match mode of 1, the formula searches for that exact date in your payout schedule, or defaults to the next larger date if an exact match isn't found.
Place your employee hire dates in column A (e.g., A2), and your sorted list of scheduled payout dates in column F (e.g., F2:F13).
Click on the target cell where you want the payout date to appear. Type the formula: =XLOOKUP(WORKDAY(A2,30),$F$2:$F$13,$F$2:$F$13,"Not Found",1) and press Enter.
If you have a list of holidays in cells H2:H20, update your formula to include this range: =XLOOKUP(WORKDAY(A2,30,$H$2:$H$20),$F$2:$F$13,$F$2:$F$13,"Not Found",1).
Select the cell with the completed formula, click the small square at the bottom-right corner of the cell (fill handle), and drag it down to apply the calculation to the rest of your employee list.
Calculate Using Helper Columns for Simplicity
If nesting formulas is too complex, split the calculation into two separate columns: one for the eligibility date and one for the payout lookup.
Effortlessly Manage Payroll Schedules with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array functions like XLOOKUP and WORKDAY. You can seamlessly calculate HR eligibility periods and payroll schedules without worrying about formula compatibility errors.
- 1. Open your payroll workbook: Launch WPS Office and open your HR employee roster or payroll spreadsheet.
- 2. Input the lookup formula: Select your target cell and type the =XLOOKUP(WORKDAY(...)) formula exactly as you would in standard spreadsheet software.
- 3. Format the output as a Date: If the result appears as a 5-digit number, right-click the cell, select 'Format Cells', choose the 'Date' category, and pick your preferred date style.
Frequently Asked Questions
Why does my WORKDAY formula return a random 5-digit number instead of a date?
Spreadsheet applications store dates as sequential serial numbers. If you see a number like '45210', simply right-click the cell, select 'Format Cells', and change the number format category from 'General' to 'Date'.
Can I calculate 30 workdays if my company's weekend is not Saturday and Sunday?
Yes. You can use the WORKDAY.INTL function instead of WORKDAY. It allows you to specify custom weekend parameters (for example, choosing Friday and Saturday as the weekend) while calculating your 30-day period.
What happens if my list of scheduled payout dates is not sorted?
When using XLOOKUP with a match mode of 1 (exact match or next larger item), an unsorted list may lead to incorrect results or errors. Always ensure your reference list of payout dates is sorted from earliest to latest.
Is XLOOKUP available in all versions of spreadsheet software?
XLOOKUP is available in newer versions of Excel (Office 365, Excel 2021) and is fully supported in WPS Office. If you are using an older spreadsheet version, you may need to use a combination of INDEX, MATCH, and COUNTIF instead.
