Dynamic Fortnightly Payroll Ending Date

Zakky

Board Regular
Joined
Mar 26, 2016
Messages
152
Office Version
  1. 365
Platform
  1. Windows
Hello, Team. Our payroll runs every two weeks. I am currently having to manually insert dates to get the 'Payroll Period - 2 week Ending Date' by using the calendar dates.
If my payroll starting week is 13th Feb 2023, then the payroll period ending date is 14 days from this date i.e. 26th Feb 2023. Then second payroll period starts on the 27th Feb 2023 and finishes 14 days later on the 12th March 2023. I am inserting the 14th Day manually at the moment. Is there a dynamic formula that calculates the 14th day from the new payroll period date e.g 27th Feb and puts it next to the cell by the daily calendar date. So all i do is +1 to the daily calendar date in the above cell and then drag it down as and when i need to update the file. Any help would be greatly appreciated. Thank you! I have uploaded an image for demonstration.
 

Attachments

  • Payroll.PNG
    Payroll.PNG
    21.2 KB · Views: 10

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Is this what you mean?

23 05 28.xlsm
AB
1
213/02/202326/02/2023
314/02/202326/02/2023
415/02/202326/02/2023
516/02/202326/02/2023
617/02/202326/02/2023
718/02/202326/02/2023
819/02/202326/02/2023
920/02/202326/02/2023
1021/02/202326/02/2023
1122/02/202326/02/2023
1223/02/202326/02/2023
1324/02/202326/02/2023
1425/02/202326/02/2023
1526/02/202326/02/2023
1627/02/202312/03/2023
1728/02/202312/03/2023
181/03/202312/03/2023
192/03/202312/03/2023
203/03/202312/03/2023
214/03/202312/03/2023
225/03/202312/03/2023
236/03/202312/03/2023
247/03/202312/03/2023
258/03/202312/03/2023
269/03/202312/03/2023
2710/03/202312/03/2023
2811/03/202312/03/2023
2912/03/202312/03/2023
3013/03/202326/03/2023
3114/03/202326/03/2023
3215/03/202326/03/2023
3316/03/202326/03/2023
3417/03/202326/03/2023
3518/03/202326/03/2023
3619/03/202326/03/2023
3720/03/202326/03/2023
3821/03/202326/03/2023
3922/03/202326/03/2023
4023/03/202326/03/2023
4124/03/202326/03/2023
4225/03/202326/03/2023
4326/03/202326/03/2023
Payroll date
Cell Formulas
RangeFormula
B2:B43B2=IF(MOD(ROWS(B$2:B2),14)=1,A2+13,B1)
 
Upvote 0
Peter_SSs, Awesome!!!! works like a dream. Many thanks and all the best!!
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,181
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top