Forum Discussion
Payroll Calendar
Thanks Sergei, but this doesn't seem to be working it's returning a circular argument error.
In cell C4 I placed the following formula, and was able to return the next pay date (15th or EOM), with A4 being the date:
=IF(DAY(A4)<=15,DATE(YEAR(A4),MONTH(A4),15),EOMONTH(A4,0))
This get's me almost all the way there, but now I need it to return the closest prior workday if the 15th or EOM are not workdays. In cell B4 I have the following WORKDAY formula that returns a "TRUE"or "FALSE" for A4: =WORKDAY(A4-1,1,$G$2:$J$11)=A4
...maybe I can leverage this too??? Thanks in advance for the help
Hi Alexander,
Formula depends on how your data is structured. If like this
you may use in column C formula
=AGGREGATE(14,6,1/--$B$2:$B$62/--($A$2:$A$62<=(EOMONTH(A2,(DAY(A2)>15)-1)+15*(DAY(A2)<=15)))*$A$2:$A$62,1)
EDIT! I started work on this before you responded. I'm glad the other solution works for you. But since I did the work I might as well post it as an alternative. Cheers
Where StartDate = first of month
Last workday on or before 15th of month:
=LOOKUP(StartDate+14, WORKDAY(StartDate+ROW($1:$15)-1,1))
Last workday on or before end of month:
=LOOKUP(EOMONTH(StartDate,0), WORKDAY(StartDate+ROW($1:$31)-1,1))Hi Craig,
Yes, even better since doesn't depend on range size. I'd only update is as
=LOOKUP(EOMONTH(A2,(DAY(A2)>15)-1)+15*(DAY(A2)<=15),WORKDAY(EOMONTH(A2,-1)+ROW($1:$35),1,$G$2:$J$11))
- Outstanding! Thank you so much for the help!
You are welcome
