First Payday Calculation using Hire Date (Bimonthly Payment)

[white_knight]

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.

 

[Tetra201]

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)

 

[white_knight]

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]

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)

 

[white_knight]

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.