Getting minimum and maximum dates from a variable length range

[ianbhenderson73]

Good morning

Before I go any further, I want to explain that I thought I knew what I was doing in Excel until I saw a spreadsheet that one of my colleagues was working on and realised that in actual fact I know very little, despite being a SQLDBA of many years' standing.

For reasons that I won't bore you with, I have a spreadsheet that I use for keeping track of my daily work activities. Each row in the spreadsheet contains details of one activity, complete with exact durations calculated as both hour & minute durations (hh:mm) and decimal values (again for reasons I won't bore you with). The row contains a lot of information, including the type of work it is (standard or overtime).

I want to create a pivot table that lets me see a breakdown of my weekly activity, broken into Standard hours and Overtime hours. Setting up the pivot table was easy enough, but one of the things I want to include is the start and end date for each week, and this is where I run into problems.

I've got a hidden column in the spreadsheet that uses the WeekNum function to determine the current week number of the date that's been entered and that works fine. However, I want to enhance it so that I know that the activity in week 23 started on 3rd June and ended on 7th June. The problem that I'm having though is getting my head around the calculations that I'd need to put in. In English, what I think I need to do is get the minimum value for date in the whole spreadsheet where the Week Number column matches the week number in the current row. I would then want to do the same again for the maximum value.

Can anyone help me?

[Fotis1991]

Can you upload a small sample workbook?

[ianbhenderson73]

Sample.xlsx

Can you upload a small sample workbook?

I've attached a sample which will hopefully show you what I'm trying to achieve.

Thanks

[Fotis1991]

Perhaps these ARRAY enterd formulas.

=MAX(IF($I$2:$I$1000=I2,$H$2:$H$1000))

=Min(IF($I$2:$I$1000=I2,$H$2:$H$1000))

[ianbhenderson73]

I've pasted those two formulae into my spreadsheet, but they're not quite right. The first one is returning the highest date in the range which is always going to be the last date entered, when it should in each case be the highest date for the week in that row. The second formula is returning the date value from column H in each row.

[ianbhenderson73]

Sorry, that last one isn't quite right. The second formula is returning the absolute minimum date for the range, so in this case it always comes back as 1st January 2013.

[Fotis1991]

OK. You are correct for min(if..We'll fix it....

But pls tell us some expected results(for both cases) explaining why.

[ianbhenderson73]

Ok. For the first data row on the spreadsheet (Row 2), I'd expect to see a date of 1st January 2013. I'd expect this to be the case until row 10 where it would change to being 2nd January 2013 because this is the first row where column I is showing a week number of 2. At the moment, the whole column shows a value of 00/01/1900, presumably because there are blanks in the data further down the range.

For the maximum date, I'd expect the value in row 2 column K to be 4th January 2013, changing in row 10 to being 11th January 2013 when the week number increases. Again, there are blanks in the data further down the range but because we're going for a maximum value these blanks don't affect the returned value.

Hope this clarifies.

TIA

[Fotis1991]

Did you confirm the formulas as ARRAYS formulas?

We get the expected results for max dates. But for min days you say..

...Ok. For the first data row on the spreadsheet (Row 2), I'd expect to see a date of 1st January 2013. I'd expect this to be the case until row 10 where it would change to being 2nd January 2013 because this is the first row where column I is showing a week number of 2

In row 10 the week number is 2 and you want as min result 2? Why? Min date for this week is 07/01/2013...

=MAX(IF($I$2:$I$1000=I2;$H$2:$H$1000))

=MIN(IF($I$2:$I$1000=I2;IF($I$2:$I$1000>0;$H$2:$H$1000)))

[ianbhenderson73]

I've now copied the formulas from your sample into my excel sheet, and confirmed that everything works.

I suspect the fault was mine: I'd forgotten to use the SHIFT+ctrl+ENTER combination when I was applying the formulas.

[Fotis1991]

I suspect the fault was mine: I'd forgotten to use the SHIFT+ctrl+ENTER combination when I was applying the formulas.

Me too! ....................