First Payday Calculation using Hire Date (Bimonthly Payment)

white_knight

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

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Tetra201

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

Replies
13
Views
218
Replies
2
Views
76
Replies
7
Views
725
Replies
5
Views
536
Replies
2
Views
170

1,127,516
Messages
5,625,252
Members
416,085
Latest member
Jlex

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.

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

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