First & Last Times

[bekyleigh]

Hi,

I'm working on developing a spreadsheet which tracks how many hours each employee is in the building for per day. I have a download from our clock in system which provides me with the employee name, dates and times they use their card to enter/leave the building. Because people are in/out all day there are several times for each date. Ultimately I am looking for a formula which will pull for each persons name their first and last clock in of each day?

I just need to formula to identify the first and last clock in times for each employee on each date and then I can develop a calculation to work out if they have done their standard hours.

My download looks like this: (I have this filtered to only my own data due to GDPR issues, but the full spreadsheet has various employee names listed in column A)

Any help is much appreciated!

 

[Roger Govier]

Hi Rebecca

for the earliest time
=MINIFS($D$692:$D$707,$B$2692:$B$707,B692)
for the latest time
=MAXIFS($D$692:$D$707,$B$2692:$B$707,B692)

Clearly , if you were doing this for each employee and for each date in the month, then I would set up a grid with Employees names in column A from row 2 down with names being repeated, once for Min and once for Maxand dates in B2 going across the page and then it would be

=MINIFS(tablename[TIME],tablename[DATE],B$2,tablename[EMPLOYEE],$A2)
=MAXIFS(tablename[TIME],tablename[DATE],B$2,tablename[EMPLOYEE],$A2)

 

[bekyleigh]

Hi Roger,

Thanks for the reply! Ive tried the above but it keeps returning with an error; #NAME?
Im using Excel 2016 (I don't know if this makes a difference)

I created absolute cell references for all the data in the same layout that I had in the original post.

Have I missed a step?

 

[Roger Govier]

Hi Rebecca
It's not you - it's Microsoft!!
They didn't include MINIFS and MAXIFS in the boxed sets of Excel 2016 - it didn't make the cut, so it is only in O365 versions which get the updates.

We can get around it by creating some array formulae using Control + Shift + Enter (CSE) instead of just Enter when you create or amend the formula.
Do NOT type the curly braces { } yourself, Excel will insert them when you use CSE

=MIN(IF((DATE=B$2)*(TIME<>0)*(EMPLOYEE=$A3),TIME))
Substitute MAX for MIN for the other formula.

Don't forget when you amend a formula press CSE.

 

[bekyleigh]

Thanks Roger!

I'm no longer getting an Error messege although it isn't returning the correct times, in fact it is retuning 0 for everything. Could this be a formatting issue? I have tried changing format of the times between 'time' and 'number' but neither bring back a result.

*I made sure I did a Ctrl+Shift+Enter for each formula *

Understandably im not expecting any return in B3, C3 or D3 as there is no data for those days, however for E3 I was expecting to see 08:31:46 ?

Thanks.

 

[bekyleigh]

Corection*

I've just realised I had the formula referencing D3 rather than A3, I have changed this but still not getting the correct return.

 

[bekyleigh]

Corection*

I've just realised I had the formula referencing D3 rather than A3, I have changed this but still not getting the correct return.

View attachment 1625

Ive also made sure I did another CSE as I realised I hadn't once I updated the formula - but I still get the 00:00:00 return

 

[Roger Govier]

Hi Rebecca
I had noticed your D3 in place of A3, which you then picked up on, but in your latest posting there are no Braces, so did you press CSE on amending?
Ah, I see another post where you say you did.
It should be $A3 and D$3 to keep the formulae correct as you copy down and across, not that it would affect the result in that one cell.

I can't see why it isn't working for you.

 

[Fluff]

Are the dates in B2:F2 actual dates, or are they text?