Formula for calculate date for payday

[Linda Nosalia]

Hi everyone, need help for the formula. So i am trying to track employee who is resign soon and need to see the employee last pay cheque.
Example payday is every 15th (for cut off last month 16-30th/31st) and every 30th/31st (cut off from this month 1-15) , bonus is every 15th (from last month 1-30/31st). Employee John is resign effective by 20 Aug 2023, so what is the formula to calculate his pay day?? thank you.

 

[awoohaw]

Hello,
You can try this, although I am not sure how you want to process the bonus (please explain).

Book1
	A	B	C
1	Last Work Day	Pay Period	Pay Date
2	2023-08-20	16-30/31	2023-09-15
Sheet1

Cell Formulas
Range	Formula
B2	B2	=IF(DAY(A2)<=15,"1-15","16-30/31")
C2	C2	=IF(DAY(A2)<=15,EOMONTH(A2,0),DATE(YEAR(A2),MONTH(A2)+1,15))

 

[Linda Nosalia]

Hello,
You can try this, although I am not sure how you want to process the bonus (please explain).

Book1
	A	B	C
1	Last Work Day	Pay Period	Pay Date
2	2023-08-20	16-30/31	2023-09-15
Sheet1

Cell Formulas
Range	Formula
B2	B2	=IF(DAY(A2)<=15,"1-15","16-30/31")
C2	C2	=IF(DAY(A2)<=15,EOMONTH(A2,0),DATE(YEAR(A2),MONTH(A2)+1,15))

Hello,
You can try this, although I am not sure how you want to process the bonus (please explain).

Book1
	A	B	C
1	Last Work Day	Pay Period	Pay Date
2	2023-08-20	16-30/31	2023-09-15
Sheet1

Cell Formulas
Range	Formula
B2	B2	=IF(DAY(A2)<=15,"1-15","16-30/31")
C2	C2	=IF(DAY(A2)<=15,EOMONTH(A2,0),DATE(YEAR(A2),MONTH(A2)+1,15))

Thank you very much for your help. So for the bonus example payday on Aug 16th for bonus calculation from July 1-31. So let said John last day is Aug 5th, i want the column formula just show the date when he will get his bonus which is on Aug 16. Hope this is good explanation . Really appreciate your help. Cheers!

 

[awoohaw]

OKay,
Try this:

Mr excel questions 54.xlsm
	A	B	C	D
1	Last Work Day	Pay Period	Pay Date	Bonus Date
2	2023-08-20	16-30/31	2023-09-15	2023-09-15
Linda Nosalia

Cell Formulas
Range	Formula
B2	B2	=IF(DAY(A2)<=15,"1-15","16-30/31")
C2	C2	=IF(DAY(A2)<=15,EOMONTH(A2,0),DATE(YEAR(A2),MONTH(A2)+1,15))
D2	D2	=DATE(YEAR(A2),MONTH(EDATE(A2,1)),15)

 

[Tetra201]

Or these more compact alternatives for the regular payday and bonus payday:

=EOMONTH(A2,0)+15*(DAY(A2)>15)
=EOMONTH(A2,0)+16