Forum Discussion

Alexander Johnson's avatar
Alexander Johnson
Copper Contributor
Oct 24, 2018

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...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 25, 2018

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

 

  • Alexander Johnson's avatar
    Alexander Johnson
    Copper Contributor
    Oct 25, 2018

    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 

     

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Oct 25, 2018

      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)

       

      • Craig Hatmaker's avatar
        Craig Hatmaker
        Iron Contributor
        Oct 25, 2018

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