# Semi-Monthly Payroll Dates

#### Huzz

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

Huzz

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### Dave Patton

##### Well-known Member

T10_1809a.xlsm
ABC
1StartEnd
2July 23, 2020July 16, 2020July 31, 2020
3July 5, 2020July 1, 2020July 15, 2020
4July 15, 2020July 1, 2020July 15, 2020
5
6d
Cell Formulas
RangeFormula
B2:B4B2=IF(DAY(A2)>15,DATE(YEAR(A2),MONTH(A2),16),EOMONTH(A2,-1)+1)
C2:C4C2=IF(DAY(A2)>15,EOMONTH(A2,0),DATE(YEAR(A2),MONTH(A2),15))

#### Huzz

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

##### Well-known Member
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.

Cell Formulas
RangeFormula
B1:B5B1=IF(DAY(A1)>15,DATE(YEAR(A1),MONTH(A1),16),EOMONTH(A1,-1)+1)
C1:C5C1=IF(DAY(A1)>15,EOMONTH(A1,0),DATE(YEAR(A1),MONTH(A1),15))

Last edited:

#### Huzz

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

Replies
4
Views
52
Replies
8
Views
134
Replies
1
Views
64
Replies
13
Views
105
Replies
1
Views
81