Forum Discussion
Payroll Calendar
I'm trying to create a payroll calendar of sorts. I've used the WORKDAY function to establish whether or not a date in a range of dates is Workday (TRUE), or not (FALSE). But now I need to be able to identify what days are paydays based on a semi-monthly schedule (15th and Last day of the Month).
Where I'm running into an issue is finding a function that allows me to round off dates, so that, if the 15th or EOM fall on a weekend or holiday (marked as FALSE by the WORKDAY function), payday will be the closet workday prior.
Hi Alexander,
If in A1 is some date when
=A1-(MOD(WEEKDAY(A1,2)-1,5)+1)*(B1>(MOD(WEEKDAY(A1,2)-1,5)+1))
returns closest workday which is not later than the date
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)
