This results in 24 pay periods over the course of a year.
For example, in the worksheet shown above, payroll dates are the 1st and 15th of each month.
In the worksheet shown above, the formula solves this problem in two steps.
Step 1 is to generate a list of all dates between the start date and the end date.
Step 2 is to filter those dates so that only those that land on the 1st and 15th remain.
This makes the formula easier to read and understand, and also improves performance.
To test each for an “end of month” date, we use theEOMONTH function.
Inside, EOMONTH, we provide 0 for months to return the last day of the month.
As before, the two expressions are joined with addition (+) to createOR logic.
In the real world companies often adjust pay dates that land on a holiday or weekend to thepreviousbusiness day.
In this configuration, WORKDAY returns the original date if it is a working day.
Otherwise, WORKDAY steps back one day at a time and returns the first working day found.
Notice that four dates have been shifted back to thepreviousworking day.
SeePrevious working dayfor an example.
If it is, IF returns the date + 14 days.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.
…
LET Function
The Excel LET function lets you define named variables in a formula.
