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 04, 2022

pottyponder  are you using Excel 365?  If you type =UNIQ do you see the UNIQUE function in the pop up help window as shown here:

 

pottyponder's avatar
pottyponder
Copper Contributor
Jul 04, 2022
Hi, I don't appear to have the unique function in Excel 2016.
  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 05, 2022
    maybe start a new thread specifically about this issue because I also have little experience in querying data from database files.
  • pottyponder's avatar
    pottyponder
    Copper Contributor
    Jul 05, 2022
    Yes I have the same problem with accdb as the mdb, there wasn't an issue when I was using Excel 2016.
  • pottyponder's avatar
    pottyponder
    Copper Contributor
    Jul 05, 2022

    Hi, this is the query that works if I remove the database password.  I am not sure where to enter it.


    Many Thanks Paul.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 05, 2022

    pottyponder 

    I have practically no experience, but if convert .mdb to .accdb is it the same issue? My guess connector doesn't work properly with .mdb files.

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 05, 2022
    That will depend on how you are doing the data import. If you are using a data query you will need to edit that query. Try going to Data->Get Data->Data Source Settings and then edit the data source you are using and edit your account or sign in using information.
  • pottyponder's avatar
    pottyponder
    Copper Contributor
    Jul 05, 2022

    Thank you so much for your help on this, I have upgraded to Excel 2021 now and have the UNIQUE function as it was going to be a pain having to enter the additional helper column.  The problem I now have is when I try to import the data from the mdb it fails with the error message "invalid password".  The database is password protected and I know the password but there is no where to enter it.  

     

    Many Thanks Paul.

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

    pottyponder as I suspected, you have an older version of Excel that doesn't have the newer functions nor dynamic arrays.  I added a helper column in each sheet to determine if that entry is unique for that day and person and then use this countifs to add them up:

    =COUNTIFS('Clacton Cash Draw record'!R:R,A2,'Clacton Cash Draw record'!AH:AH,TRUE)+
COUNTIFS('Dovercourt CashDrawerRecord'!P:P,A2,'Dovercourt CashDrawerRecord'!AH:AH,TRUE)+
COUNTIFS('Ipswich CashDrawerRecord'!P:P,A2,'Ipswich CashDrawerRecord'!AH:AH,TRUE)

Resources