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