Semi-monthly payroll date formula

[talkwald]

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:

 

[Domenic]

Try the following...

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

Hope this helps!

 

[Aladin Akyurek]

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)))

 

[talkwald]

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