# Semi-monthly payroll date formula

#### talkwald

##### Board Regular
I'm trying to auto-calc the end date of semi-monthly payoll. So if the Begin Date (cell A1) is Mar-01 (March 1st), then the End Date (cell A2) would be Mar-15. If the Begin Date (cell A1) is Mar-15, then the End Date (cell A2) would be Mar-31....the last day of the month. And so on for a full year. The formula I am trying is the following:
=IF(A1="MMM-01",DATE(YEAR(A1),MONTH(A1)+1,0),DATE(YEAR(A1),MONTH(A1),15)). The formula works fine if A1 is Mar-01, but it does work if cell A1 is Mar-15.

Can anyone help me?

Thanks, Barbara ray:

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Try the following...

=IF(DAY(A1)<15,DATE(YEAR(A1),MONTH(A1),15),DATE(YEAR(A1),MONTH(A1)+1,0))

Hope this helps!

In A2 enter & copy down:

=IF(DAY(A1)=1,DATE(YEAR(A1),MONTH(A1),15),DATE(YEAR(A1),MONTH(A1)+1,IF(DAY(A1)>=28,15,0)))

Hey -- That works great!!! Thanks SO MUCH, Dominec. Aladin, your's is slightly different. I'll try it too-so thanks as well. The more resources, the better!,
Barbara

Replies
13
Views
741
Replies
16
Views
567
Replies
5
Views
221
Replies
3
Views
178
Replies
1
Views
137

1,203,757
Messages
6,057,178
Members
444,911
Latest member
Uncommon1

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

### Which adblocker are you using?

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