Forum Discussion

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
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.
Competition-2.xlsx1.6 MB

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

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.

Competition-2.xlsx1.6 MB

pottyponder
Copper Contributor
Jul 04, 2022
Good Morning, thanks again for your continued help. If I refresh the formula it returns a 0's for the days worked.

Many thanks Paul.
- 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
    Copper Contributor
    Jul 04, 2022
    Hi, I don't appear to have the unique function in Excel 2016.