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