• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating pay period ending date

In the attached spreadsheet, a pay period ends on every other Friday (yellow). I am trying to come up with a formula that will determine the pay period ending date for any given date, i.e. for any date, round to the next highest pay period ending date.

I feel like I am somewhat close, but when I vary the 2nd argument in the WEEKDAY function, my results shift by 7. I also tried adding 14 to the current date instead of 7.

I am working on row 8.
 

Attachments

  • Chandoo Pay Period Ending Date.xlsx
    10 KB · Views: 0
Hi ,

I am not sure about your question , since changing the second parameter of the WEEKDAY function will change the results that you get.

If you wish to use the default syntax of the WEEKDAY function , then :

=Anydate - WEEKDAY(Anydate - 6) + 7

will give you the next Friday.

Narayan
 
Hi ,

I am not sure about your question , since changing the second parameter of the WEEKDAY function will change the results that you get.

If you wish to use the default syntax of the WEEKDAY function , then :

=Anydate - WEEKDAY(Anydate - 6) + 7

will give you the next Friday.

Narayan

Thank you for your suggestion, but I am trying to get the results to return every other Friday (in yellow). I can't figure out how to skip every other Friday.
 
So you mean the pay periods are every two weeks and not on a weekly basis, for example if we're on day 10-Jan-2015, next pay end date will not be following friday (16-Jan-2015) but the one afterwards (23-Jan-2015), is that it?

If that's the case, just simply adjust Narayank's formula:
=Anydate - WEEKDAY(Anydate - 6) + 14
 
So you mean the pay periods are every two weeks and not on a weekly basis, for example if we're on day 10-Jan-2015, next pay end date will not be following friday (16-Jan-2015) but the one afterwards (23-Jan-2015), is that it?

If that's the case, just simply adjust Narayank's formula:
=Anydate - WEEKDAY(Anydate - 6) + 14

Yes, every other Friday. I tried your suggestion, but it didn't work.
 
Hi ,

I asked whether the method is acceptable ; eliminating helper columns is a different matter altogether , which we can come to once you confirm that the method is acceptable.

Narayan
 
Hi ,

It is also a different matter that the formula used does not make use of any helper columns ; the columns A , C , D and E are irrelevant to the final formula in column F.

Only two named ranges have been used. See this file.

Narayan
 

Attachments

  • Chandoo Pay Period Ending Date.xlsx
    13.4 KB · Views: 2
Last edited:
Back
Top