Finding the Earliest and Latest Dates for each Task Code

I receive a report with data similar to the attached.

Example 1.xlsx

For each Task code I need to extrapolate the Earliest and Latest Dates which work hours spent has taken place against each Task Code.

I prefer to create a VBA code I can place in columns “E” & “F”, but if anyone can show me a formula to do this I would be grateful.

For Task Code 1000:

Earliest Date would be “12/20/2013” (The formula would be in cell “E2”)

Latest Date would be “10/30/2014” (The formula would be in cell “F2”)

For Task Code 2000:

Earliest Date would be “5/30/2013” (The formula would be in cell “E13”)

Latest Date would be “8/4/2014” (The formula would be in cell “F13”)

Judgeh59,

Thanks for your quick response, but the report is a little more in depth to just use a Min/Max Formula.
What I failed to mention is that there are about a thousand Task codes in my report with variable amounts of entries per Category.
For me to write that formula for each task code every time this report is ran isn't reasonable.

Richard,

Thanks for the Quick response.

This worked Perfect! Thanks!

Originally Posted by Richard Buttrey

My pleasure.

I forgot to add that you could of course create a macro to populate E2:F2 with those formulae and then copy and paste it down to the last row.

Hopefully you picked up the typo on F2 where I had written $A3:B$100 rather than $A3:A$100

Just tried it in my actual report and the formula didn't work the same. Can I Send you a small portion of my report to see if you can see why?
Ben

Originally Posted by Richard Buttrey

My pleasure.

I forgot to add that you could of course create a macro to populate E2:F2 with those formulae and then copy and paste it down to the last row.

Hopefully you picked up the typo on F2 where I had written $A3:B$100 rather than $A3:A$100

I am attaching a bigger example with some real data. In column "H" & "I" I have entered your formula and in Column "L" & "M" I have entered what the solution should be.

Thanks for your time,
Ben

Originally Posted by Richard Buttrey

Hi,

The difference is column A. In the first example these were all numeric values. This latest are all text strings.
This complicates the formulae somewhat and they need to be changed as follows

H2

Formula:

Please Login or Register  to view this content.

I2

Formula:

Please Login or Register  to view this content.

NB. These are both array formulae and need to be entered with Ctrl Shift Enter

SUCCESS!

I had to Research array formulas and understand how Ctrl+Shift+Enter worked, but it works great now.

Thanks for your help and patients,
Ben