Semi-monthly Pay Date with Different Pay Period

[wasatchgirl]

Payroll for my company is semi-monthly. The workdays 8th - 23rd are paid on the following 1st. The workdays 24th - 7th are paid on the following 15th. How do I use =today() to determine its corresponding payroll date? I've created the following that appears to work. Is there anything simpler or more elegant? Always trying to learn!

A1 = TODAY()

B1 =IF(AND(DAY(A1)>=8,DAY(A1)<=23),(EOMONTH(A1,0))+1,IF(DAY(A1)>8,EOMONTH(A1,0)+15,EOMONTH(A1,-1)+15))

Any thoughts are super appreciated!
Thanks!

 

[VBA Geek]

one alternative:

=DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>7),LOOKUP(DAY(A1),{1,8,24},{15,1,15}))

 

[markmzz]

Hi!

Another way:

=IF(DAY(A1)>=8,EOMONTH(A1,0)+IF(DAY(A1)<=23,1,15),EOMONTH(A1,-1)+15)

Markmzz