CODEWITHSUNDEEP
excelweekday
Alex Huntington
Alex Huntington

Reputation: 23

How to find the following Friday after a pay period ends

My company pays every Friday on the sales from the previous week. Our work period goes from Sunday to Saturday, and we get paid every following Friday for any work done in that pay period.

I have the dates of my sales in column J, and I want the corresponding payday to appear in column M. So if J1 had 1/14/17, M1 would return 1/20/17. The formula I found is:

=J1+7-WEEKDAY(J1+1)+7.

This works almost perfectly except that any sales made on a Saturday don't get paid out for 13 days, and they should get paid out in 6 days. So how do I change this to get what I need?

Upvotes: 1

Views: 134

Answers (1)

Stephen Rauch
Stephen Rauch

Reputation: 49774

The element that snaps a particular day to a particular week is the weekday() function. So you need to adjust the input to weekday function by one day and then compensate by adding one less day at the end.

So you need:

=J1-WEEKDAY(J1)+13

Instead of:

 =J1-WEEKDAY(J1+1)+14

Upvotes: 3

Related Questions