Help with time function

[Nitya Satheesh]

Hi!

I have a spreadsheet filled with data that includes date and time. For a particular value in column a i need to find the max and min time.

My sheet looks a little like this

Column a columnb
1234 2/01/2013
1234 3/01/2013
1234 3/01/2013
1234 4/01/2013
1234 5/01/2013
1234 6/01/2013
5678 1/01/2012
5678 2/01/2012
5678 3/01/2012
5678 4/01/2012

So I need to find the max and min dates for the values 1234 and 5678 etc,.

Please help!
Thanks!

 

[Ron Rosenfeld]

Hi!

I have a spreadsheet filled with data that includes date and time. For a particular value in column a i need to find the max and min time.

My sheet looks a little like this

Column a columnb
1234 2/01/2013
1234 3/01/2013
1234 3/01/2013
1234 4/01/2013
1234 5/01/2013
1234 6/01/2013
5678 1/01/2012
5678 2/01/2012
5678 3/01/2012
5678 4/01/2012

So I need to find the max and min dates for the values 1234 and 5678 etc,.

Please help!
Thanks!

There are several ways to do this, depending on what your real data looks like.

You can use a Pivot Table
Insert Pivot Table
Drag Col A to the Rows Area
Drag Col B to the Values area twice
Click the drop down arrow on the Col B items to get to the Value Settings Menu
Set one to Min and the Number Format to Date
Set the other to Max and the Number Format to Date


Values
Row Labels Max of Col B Min of Col B
1234 6/1/13 2/1/13
5678 4/1/12 1/1/12

If your values are really in sorted order as above, with the both columns sorted as you show, then you can do something like

F2: 1234
F3: 5678

For Max G2: =LOOKUP(2,1/(Col_A=F2),Col_B)
or you might try: =VLOOKUP(F2,Col_A:Col_B,2)
For Min H2: =VLOOKUP(F2,Col_A:Col_B,2,FALSE)

 

[joeu2004]

My sheet looks a little like this
Column a columnb
1234 2/01/2013
1234 3/01/2013
1234 3/01/2013
1234 4/01/2013
1234 5/01/2013
1234 6/01/2013
5678 1/01/2012
5678 2/01/2012
5678 3/01/2012
5678 4/01/2012
So I need to find the max and min dates for the values 1234 and 5678 etc,.

Array-enter the following formulas (press ctrl+shift+Enter instead of just
Enter):

=MIN(IF($A$1:$A$10=1234,$B$1:$B$10))

=MAX(IF($A$1:$A$10=1234,$B$1:$B$10))

To confirm that you array-entered the formula, verify that the formula is
surrounded by curly braces (e.g, {=MIN(IF(...))} in the Formula Bar when you
select the cell. Note that we cannot typye the curly braces ourselves.
That is just Excel's way of denoting an array-entered formula.

 

[Nitya Satheesh]

Thanks a Lot!!! That worked great!!!

I have one more query with respect to the same problem.What if I wanted to add one more column of data.

Column a column b column c
1234 1 2/01/2013
1234 1 3/01/2013
1234 1 4/01/2013
1234 2 4/01/2013
1234 2 6/01/2013
1234 3 6/01/2013
5678 3 8/01/2012
5678 8 2/01/2012
5678 8 3/01/2012
5678 2 4/01/2012
5678 2 5/01/2012
So now what I want to do is get the max and min date for each of the values in column b, and then the total number of days for each of the values in column a.
for e.g from the above data I know that '1' will have a max and min date of 4/01/2013 and 2/01/2013 so the number of days is 2, '2' will have a number of days of 2 and 3 will have a number of days 2 so 1234 will have a total days of 6.
Please help.
Thanks in advance.

 

[Ron Rosenfeld]

Thanks a Lot!!! That worked great!!!

Who are you responding to, and, if to me, which method are you using?

Please also show an example as to how you would like your results to be presented.

If you are using my Pivot Table method, I would probably just set up a second Pivot table.

 

[Ron Rosenfeld]

Thanks a Lot!!! That worked great!!!

I have one more query with respect to the same problem.What if I wanted to add one more column of data.

In addition to my previous response, if you are using my formulas, it is simple to adapt them to your requirements.

For the number of days for 1234, with

F2: 1234
G2: =COUNTIF(Column_a,F2)

And for the Max and Min of the values in column_b, merely adapt the formula I previously posted for the Max and Min of the values in column A:

e.g.

F6: 1
G6 (MIN): =VLOOKUP(F6,column_b:column_c,2,FALSE)
H6 (MAX): =LOOKUP(2,1/(column_b=F6),column_c)

You should be able to figure out how to use 2, 3 and 5678.

If you want different results, you will need to, as I mentioned before, be specific as to how you want the results presented.

 

[Nitya Satheesh]

In addition to my previous response, if you are using my formulas, it is simple to adapt them to your requirements.



For the number of days for 1234, with



F2: 1234

G2: =COUNTIF(Column_a,F2)



And for the Max and Min of the values in column_b, merely adapt the formula I previously posted for the Max and Min of the values in column A:



e.g.



F6: 1

G6 (MIN): =VLOOKUP(F6,column_b:column_c,2,FALSE)

H6 (MAX): =LOOKUP(2,1/(column_b=F6),column_c)



You should be able to figure out how to use 2, 3 and 5678.



If you want different results, you will need to, as I mentioned before, be specific as to how you want the results presented.

Thank You! Sorry I was not more specific. I used The Pivot table method and just set up another Pivot table.

Thanks again.

 

[Nitya Satheesh]

Array-enter the following formulas (press ctrl+shift+Enter instead of just

Enter):



=MIN(IF($A$1:$A$10=1234,$B$1:$B$10))



=MAX(IF($A$1:$A$10=1234,$B$1:$B$10))



To confirm that you array-entered the formula, verify that the formula is

surrounded by curly braces (e.g, {=MIN(IF(...))} in the Formula Bar when you

select the cell. Note that we cannot typye the curly braces ourselves.

That is just Excel's way of denoting an array-entered formula.

Thank you!