Forum Discussion

pottyponder's avatar
pottyponder
Copper Contributor
Jun 30, 2022
Solved

Employee's working days by entry

Hi, I am trying to calculate how many days my employee's have worked each month from my transaction journal.  Each day there are lines of entries which all have an operator code in a specific column,...
excel
  • mtarler's avatar
    mtarler
    Jul 03, 2022

    pottyponder  oops, my bad  I didn't account for them being date-time stamps and not just date.  Here is the updated:

    =IFERROR(ROWS(UNIQUE(FILTER(INT('Clacton Cash Draw record'!U:U),'Clacton Cash Draw record'!R:R=Figures!A2))),0)+
IFERROR(ROWS(UNIQUE(FILTER(INT('Dovercourt CashDrawerRecord'!T:T),'Dovercourt CashDrawerRecord'!P:P=Figures!A2))),0)+
IFERROR(ROWS(UNIQUE(FILTER(INT('Ipswich CashDrawerRecord'!T:T),'Ipswich CashDrawerRecord'!P:P=Figures!A2))),0)

    a couple nuances include:

    a) if the same person worked at 2 DIFFERENT locations (i.e. listed on 2 different sheets) the same day)

    b) if the person works after midnight (i.e. 12:30am the 'next' day) it will count as the next day (I didn't check to see if this is even an issue in your data.

mtarler's avatar
mtarler
Silver Contributor
Jul 01, 2022
This sounds like you want to use a SUMIFS() or COUNTIFS() function but I'm really struggling to understand the sheet format/set-up. Could you please attach a sample or provide a link to a sample sheet.
pottyponder's avatar
pottyponder
Copper Contributor
Jul 03, 2022

Hi, thanks for responding.  I have attached the spreadsheet and a message on the first page explaining what I am trying to achieve.

 

Many Thanks Paul.

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 03, 2022

    pottyponder I notice you have multiple locations and those initials may appear multiple times in the same day so in the following I filtered the date based on initials then found unique dates and counted for each sheet.  The formula does NOT remove multiple entries on the same day at 2 different locations. With the new text functions in Beta I'm sure it could be done easily but it is more complicated without those and I don't even know if you want them to count or not...

    =IFERROR(ROWS(UNIQUE(FILTER('Clacton Cash Draw record'!U:U,'Clacton Cash Draw record'!R:R=Figures!A2))),0)+
IFERROR(ROWS(UNIQUE(FILTER('Dovercourt CashDrawerRecord'!T:T,'Dovercourt CashDrawerRecord'!P:P=Figures!A2))),0)+
IFERROR(ROWS(UNIQUE(FILTER('Ipswich CashDrawerRecord'!T:T,'Ipswich CashDrawerRecord'!P:P=Figures!A2))),0)

     

    • pottyponder's avatar
      pottyponder
      Copper Contributor
      Jul 03, 2022
      Hi, thank you for your help. This is counting each entry as a day worked, there are multiple entries for each employee each day. I need it to count as 1 if there is an entry on a day. Sorry to be a pain.

      Thanks Paul.
      • mtarler's avatar
        mtarler
        Silver Contributor
        Jul 03, 2022

        pottyponder  oops, my bad  I didn't account for them being date-time stamps and not just date.  Here is the updated:

        =IFERROR(ROWS(UNIQUE(FILTER(INT('Clacton Cash Draw record'!U:U),'Clacton Cash Draw record'!R:R=Figures!A2))),0)+
IFERROR(ROWS(UNIQUE(FILTER(INT('Dovercourt CashDrawerRecord'!T:T),'Dovercourt CashDrawerRecord'!P:P=Figures!A2))),0)+
IFERROR(ROWS(UNIQUE(FILTER(INT('Ipswich CashDrawerRecord'!T:T),'Ipswich CashDrawerRecord'!P:P=Figures!A2))),0)

        a couple nuances include:

        a) if the same person worked at 2 DIFFERENT locations (i.e. listed on 2 different sheets) the same day)

        b) if the person works after midnight (i.e. 12:30am the 'next' day) it will count as the next day (I didn't check to see if this is even an issue in your data.

Resources