I'm working on a budget spreadsheet and I'd like to calculate the number of pay periods before a certain date (including that date if it's a pay day). I'm not really sure how to begin.
My pay periods are always the same length of time, every other Friday. I was thinking I would somehow enter in a pay date, and from there the formula could count the number of pay days from today until another date.
As an example, let's say today is 05/22/2016. My next pay date is 06/03/2016. If I have something due on 12/13/2016, I want the formula to state that there are 14 pay days between now and then. If I were to recalculate from 06/04/2016, then it should say 13.
How can I count the number of pay days between two dates in Excel?
Best Answer
Here's the equation:
Description:
The day of the week you get paid needs to be put in cell
B1
encoded as Sunday=1, Monday=2, .. ,Saturday=7.The cell
B2
should have the starting date.The cell
B3
should have the end date.How often you get paid in days needs to go cell
B4
(i.e. once per week=7,every other week=14).This will not count the current payday if the start day is a payday. To remove that, just use
=INT(DAYS(B3,B2)/B4))
.