Payroll Calendar Calculation ??!! Help?

InFeKtId

New Member
Joined
Sep 3, 2014
Messages
9
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 NameHIRE DATE 6 Months1 Year 2 years 3 Years6 Years
Test Testington 8/4/2014Date here Date hereDate hereDate hereDate 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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Could you not simply use
Code:
=B2 + 14
 

InFeKtId

New Member
Joined
Sep 3, 2014
Messages
9
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

New Member
Joined
Sep 3, 2014
Messages
9
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

Well-known Member
Joined
Oct 17, 2007
Messages
2,287

ADVERTISEMENT

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

InFeKtId

New Member
Joined
Sep 3, 2014
Messages
9
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

Well-known Member
Joined
Oct 17, 2007
Messages
2,287

ADVERTISEMENT

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
 

InFeKtId

New Member
Joined
Sep 3, 2014
Messages
9
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

Well-known Member
Joined
Oct 17, 2007
Messages
2,287
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

New Member
Joined
Sep 3, 2014
Messages
9
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,051
Messages
5,639,773
Members
417,112
Latest member
PachRedoc

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
Top