Semi-Monthly Payroll Dates

[Huzz]

Hello,

I am using a formula to calculate the end of a payperiod for semi-monthly payroll dates EX: 01/01/20 to 01/15/20 Paid on 01/25/20 and 01/16/20 to 01/31/20 paid on 02/10/20. I use a calculateion that gives me the end dates of a payperiod but I need one that will give me both the start dates and the end dates.

This is a formula I got off of Mr. Excel: =IF(DAY(M1)<15,DATE(YEAR(M1),MONTH(M1),1),DATE(YEAR(M1),MONTH(M1)+1,0))
This is great for calculating the end date but I need to calculate the start date also and sometimes I need to calculate both start and and dates. Can someone help? I am awful with date formulas.

Thanks in advance,

Huzz

 

[Dave Patton]

your challenge is not clearly defined. Hopefully the following is helpful.

T10_1809a.xlsm
	A	B	C
1		Start	End
2	July 23, 2020	July 16, 2020	July 31, 2020
3	July 5, 2020	July 1, 2020	July 15, 2020
4	July 15, 2020	July 1, 2020	July 15, 2020
5
6d

Cell Formulas
Range	Formula
B2:B4	B2	=IF(DAY(A2)>15,DATE(YEAR(A2),MONTH(A2),16),EOMONTH(A2,-1)+1)
C2:C4	C2	=IF(DAY(A2)>15,EOMONTH(A2,0),DATE(YEAR(A2),MONTH(A2),15))

 

[Huzz]

Hi Dave,

Thank you for the super quick response. I think what I need is columns B & C to give me the start and end of the payperiods. for example if I enter a date 5/5/20 I need the result to tell me start 5/1/20 and end 5/15/20. If I enter the date 5/27/20 I need it to Start 5/16/20 and end 5/30/20. The first one you sent B2 seems to work but the second one give me a result of 15 no matter what date I use. I really really appreciate your help!

 

[Dave Patton]

Ensure that you enter actual dates in Column A.
The XL2BB is converting Column A to text.
Coerce the text to dates or delete the text, change the format to general, and re-enter the dates.
Copy or fill the formulas down.

T10_1809a.xlsm
	A	B	C
1	23-Jul-20	July 16, 2020	July 31, 2020
2	5-Jul-20	July 1, 2020	July 15, 2020
3	15-Jul-20	July 1, 2020	July 15, 2020
4	5-May-20	May 1, 2020	May 15, 2020
5	27-May-20	May 16, 2020	May 31, 2020
6d

Cell Formulas
Range	Formula
B1:B5	B1	=IF(DAY(A1)>15,DATE(YEAR(A1),MONTH(A1),16),EOMONTH(A1,-1)+1)
C1:C5	C1	=IF(DAY(A1)>15,EOMONTH(A1,0),DATE(YEAR(A1),MONTH(A1),15))

 

[Huzz]

Thank you Awesome Dave.... I can't tell you how helpful this solution is! Have the very best week new friend