Payroll Calendar Calculation ??!! Help?

[InFeKtId]

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>

 

[Michael M]

Could you not simply use

=B2 + 14

 

[InFeKtId]

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?

 

[InFeKtId]

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?

 

[konew1]

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

 

[InFeKtId]

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

 

[konew1]

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
	A	B	C	D	E
2	Base Pay Friday
3	Friday, 25 July 2014
4
5	Start	First pay	6 Mths	1 YR	2 Yrs
6	Saturday, 6 September 2014	Friday, 19 September 2014	Friday, 18 September 2015	Friday, 16 September 2016	Friday, 14 September 2018
7	Thursday, 18 September 2014	Friday, 19 September 2014	Friday, 18 September 2015	Friday, 16 September 2016	Friday, 14 September 2018
8	Friday, 19 September 2014	Friday, 3 October 2014	Friday, 2 October 2015	Friday, 30 September 2016	Friday, 28 September 2018
Sheet2

 

[InFeKtId]

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.

 

[konew1]

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.

 

[InFeKtId]

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