First Payday Calculation using Hire Date (Bimonthly Payment)

white_knight

New Member
Joined
Jun 16, 2017
Messages
3
Hello Everyone,

I want to get the first payroll of the new hires by using their hiring dates. The pay is given on the 15th and last day of the month. I am using the formula IF(DAY(Hiring Date)<=15,DATE(YEAR(Hiring Date),MONTH(Hiring Date),15),EOMONTH(F4,0)). It gives me their first payroll to be either 15th or last day of the month. The only issue is that this formula doesn't exclude the weekend and holidays. I want the formula to readjust the date (make the payday earlier) if payday falls on the weekend or holiday. I am trying to use Weekday and Workday functions, but getting nowhere. Please help. Thank you in advance.
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
Assuming you have your holiday dates in a range named Holidays, try this:

=WORKDAY(EOMONTH(A1,-(DAY(A1)<=15))+15*(DAY(A1)<=15)+1,-1,Holidays)
 
Last edited:

white_knight

New Member
Joined
Jun 16, 2017
Messages
3
Thank you so much. This works perfectly. I have one more modification if you could help me with that as well, please. So, according to our policy for PTO, I have to add 90 days to the hiring date and then calculate the first pay day using the resulting date. The formula you gave me works fine, as I have one cell for adding those 89 days and then I use your formula to find the first payday. There is a small issue. Let me give you an example. If the candidate is hired on 10/2/2017, then adding 89 days would give 12/30/2017. Now using your formula I'm getting 12/29/2017 (12/30/2017 is a Saturday). However, I'd like to get 15th of January 2018 as the employee is eligible for PTO on the first payroll post 90 days of employment. Thus, the first payroll has to be the 15th of January now as the previous payroll was adjusted due to the weekend. Is it possible to calculate that formula? Thanks
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
You are most welcome.


See if the following works for your PTO eligibility calculations.

Instead of adding 89 days to the hiring date (=Hiring_Date+89), try using

=WORKDAY(Hiring_Date+88,1,Holidays)


As an alternative, try this long formula (assuming cell B1 has =Hiring_Date+89):

=WORKDAY(EOMONTH(WORKDAY(B1-1,1,Holidays),-(DAY(WORKDAY(B1-1,1,Holidays))<=15))+15*(DAY(WORKDAY(B1-1,1,Holidays))<=15)+1,-1,Holidays)
 
Last edited:

white_knight

New Member
Joined
Jun 16, 2017
Messages
3
You're awesome. This worked perfectly. Thank you once again. I'll reach out to you in case I find a new variable. Have a good day.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,997
Messages
5,622,083
Members
415,875
Latest member
Tarali

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