Finding Min and Max Times for Each Date

[Sully1440]

Hi,
I need help with a formula to find the Earliest Time and Latest Time and finally the difference between each of these times (in hours) based on a data set that has a list of Dates and several times for each date.

Basically, in the attached spreadsheet, I want to find the min time and max time for each Date for each Employee.

I'm thinking it could possibly be an Index and Match formula with an If Max Statement but I have no clue how to do this.

Any help would be appreciated.

Thx,
Jim

[mattreingold]

Is this in the wrong thread?

[Sully1440]

Not sure I know what you mean by wrong thread.

So far I figured out the following:

=MIN(IF($C$2:$C$21000=C2,$E$2:$E$21000,""))

=MAX(IF($C$2:$C$21000=C2,$E$2:$E$21000,""))

But it looks at the entire sheet. I want the result to bring back info specific to EmpNo.

[Sully1440]

Solved it....

=MAX(IF(C$2:C$1000=C2,IF(N$2:N$1000=N2,E$2:E$1000)))

[p45cal]

In the attached a suggestion only using one simple formula on the Summary (Counts) sheet.
There is however a pivot table, which took 2 minutes to set up, in Columns M:P, which I've coloured grey, and next to it in column Q (green) a simple subtraction formula giving daily work time.
However, although the data is neatly summarised with one day per row, because your dates in column (C) are not real Excel dates (they're just text), the data is difficult to sort chronologically.

On a second sheet (Summary (Counts) (2)), I've first used a Text To Columns on column B using Date: DMY in Step 3 of 3 of the dialogue box, which converts the column to real Excel dates, conserving the time element too (without adding extra columns either). Then some simple formulae in columns C and D to split the date and time elements. The important part here is that the date column (C) contains real Excel dates, which means the dates are easily sorted chronologically. In columns G and H I've used MaxIFs and MinIFs formulae (easier to understand, don't need to be array-entered and much lighter on resources/calculation time) to get the earliest and latest times per day/EmpNo. Finally a simple subtraction in column I to get work time.

This makes the pivot table even easier to put together, but also allows a timeline to be added; a flexible control which means you can very quickly select the time frame of the data you see.

I've also added a slicer for EmpNos, so you can quickly narrow down what you're looking at.

[p45cal]

I've just realised there's an even easier way to split the date/time to date and time in separate columns, requiring no formulae and resulting in proper Excel recognition of dates and times:
2018-07-23_021025.jpg

2018-07-23_021141.jpg

2018-07-23_021232.jpg

2018-07-23_021400.jpg

[Sully1440]

Hi p45cal,
This is cool. The Text to Columns thing is really neat to break the date up into date and time. I like it.
When I used my previous formula .....=MAX(IF(C$2:C$1000=C2,IF(N$2:N$1000=N2,E$2:E$1000)))...... the array uses a huge amount of resources/calculation time. My computer just lags and it takes forever to get a solution. So I'm very interested in the MaxIFs and MinIFs formulae that you used.

Here's my problem: When I open the spreadsheet, I get an error on the 2nd tab for the Earliest and Latest columns G and H. The formula "=_xlfn.MINIFS($D$2:$D$5858,$C$2:$C$5858,$C2,$F$2:$F$5858,$F2)" doesn't work. It brings back "#NAME?". Is it because I'm using a Excel Office 10?

BTW, thx for this
Jim

[p45cal]

Yes, they're not available in 2010/2013. Aggregate is.

Try this normal (not array-entered) formula for Minifs (earliest time):
=AGGREGATE(15,6,$C$2:$C$5858/(($H$2:$H$5858=$H2)*($B$2:$B$5858=$B2)),1)
and this for MaxIfs:
=AGGREGATE(14,6,$C$2:$C$5858/(($H$2:$H$5858=$H2)*($B$2:$B$5858=$B2)),1)
(Column H had the EmpNos, Column B dates, Column C times.)

I expect it to be lighter but not 100% sure. It seems a lot faster here.

But…
you don't really need these formulae at all, the pivot can do it for you, and I'm near certain that it's lighter still on resources.

The attached is an Excel 2010 compatible version (I hope). Columns B & C produced courtesy of Text-to-Columns.

There are two pivots:
The first, the lightest solution I think, is in columns K:O, and only needs the formula-less columns B,C & D (Date, Time & EmpNo) - nothing else. A trivial formula in column O.

The second solution at column S onwards, needs all columns in the source data apart from Sequence and Description #1 but has the advantage of being self-contained (no calculated column outside the pivot). I've addded some grouping to the Date field in the pivot to allow quick selection of dates via slicers (there are no Timelines in Excel 2010). You could do something similar for the first solution.

ps. the lightest solution by itself in a file comes in at 310kb, which is less than 60% of your original file's size.

[Sully1440]

Awesome

I'm going to use the Pivot Tables for my file/report. I got the other formula to work too (Aggregate and Arrays), but its heavy on resources. Aggregate, MAXIFS, and Arrays are brand new to me, so this is very good to know.

p45cal....Very much appreciated

Thank you,
Jim