# Payroll Calendar Calculation ??!! Help?

#### InFeKtId

##### New Member
Hello,

What im trying to do is create a Excel Form that will display the dates of our new hires raise points Per pay period. example:

 Employee Name HIRE DATE 6 Months 1 Year 2 years 3 Years 6 Years Test Testington 8/4/2014 Date here Date here Date here Date here Date here

<tbody>
</tbody>

I need it to land on every friday bi weekly for each pay period avoiding landing on weekends and holidays of course.

I think I have the first part kind of heading in the right direction, but i'm not very good with this date thing on excel. I'm alright with the simple stuff but this is OMH?

This is what I have so far .... no much.... =\$B2-WEEKDAY(\$B2)+13 this seems to be landing on Fridays and no matter what the start date is its landing on the next bi weekly pay period. It seems to be picking the right ones from what I tested but you guys are the experts

Thanks in advance

<tbody>
</tbody>

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Could you not simply use
Code:
``=B2 + 14``

No, the =B4 + 14 makes the date land on a Monday. The formula I have seems to be doing what I want BUT I don't know a formula that will add 6 months, 12 months, 24 months etc.. to the start date?

No, the =B4 + 14 makes the date land on a Monday. The formula I have seems to be doing what I want BUT I don't know a formula that will add 6 months, 12 months, 24 months etc.. to the start date?

If you add 6 months you won't keep to a Friday.
Maybe go C2+182 to get the Friday 6 months out.

That doesn't seem to be working either it's jumping on the wrong payday. I just determined that my formula is no good either, it's not working for some dates. It lands on a Friday but doesn't land on our paydays? ugh

let me try and layout exactly what im needing..... I need to input a Date of Hire then I need excel to calculate (Regardless of the date) the nearest/first Bi-weekly payday. Then, excel needs to calculate in the rest of the cells/columns the 6 month anniversary date Which should land on a Payday Friday, then 1 year anniversary date etc.etc....

Try this. You need to identify which Fridays are pay week. I have used A3 to house a any pay Friday as a constant.

Excel Workbook
ABCDE
2Base Pay Friday
3Friday, 25 July 2014
4
5StartFirst pay6 Mths1 YR2 Yrs
6Saturday, 6 September 2014Friday, 19 September 2014Friday, 18 September 2015Friday, 16 September 2016Friday, 14 September 2018
7Thursday, 18 September 2014Friday, 19 September 2014Friday, 18 September 2015Friday, 16 September 2016Friday, 14 September 2018
8Friday, 19 September 2014Friday, 3 October 2014Friday, 2 October 2015Friday, 30 September 2016Friday, 28 September 2018
Sheet2

That doesn't seem to work either... IDK even if it is possible. To do so you would have to be an EXCEL MASTER???

I need this thing to calculate to our next payday(regardless of start date) and round up to the nearest one. Then calculate six months in the future to the next nearest paydate then One Year and Two Year and Three Year and 5 Year etc.

Looking now I see my formula for column C (6 months) actually runs for a year. Change it to 13 fortnights and it should be good. Same for column D.

Looking now I see my formula for column C (6 months) actually runs for a year. Change it to 13 fortnights and it should be good. Same for column D.

THANK YOU!!! you are a life saver that all seems to be working now..... Now if i may ask one more favor for anyone?

When I copy this code to the cells below it, IF the reference Cell is empty it puts dates in all the cells that (exp. 1/1/1900) how can I safely Convert these formulas into the IF CellBlank Fomula?

HERE IS THE FILE : http://www.filedropper.com/accrualscalculations

Replies
11
Views
287
Replies
3
Views
933
Replies
3
Views
953
Replies
1
Views
370
Replies
2
Views
354

Threads
1,203,116
Messages
6,053,604
Members
444,673
Latest member
DWriter9

### 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

### 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