Need help to find oldest and latest date

[psdileep]

I've data in two columns as below and I need formula to get latest and oldest date as below:

1) Find oldest date from columns having data in both Col 1 and Col 2
2) Find newest date from columns having only date in Col 2

Col 1 Col 2
A 2014-03-19
B 2014-03-19
C 2014-03-19
D 2014-03-19
E 2014-03-19
F 2014-08-28
A 2014-08-28
B 2014-08-28
C 2014-08-28
D 2014-08-28
E 2014-08-28
F 2014-08-28
2014-08-28
2014-08-28
2014-08-28
2014-08-28
2014-08-27
2014-08-27

 

[Peter_SSs]

Does this do what you want?

If using Excel 2010 or later you can use the col F formulas, otherwise use col G formulas which must be confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}

Excel Workbook
	A	B	C	D	E	F	G
1	Col 1	Col 2
2	A	2014-03-19			Oldest with 2	2014-03-19	2014-03-19
3	B	2014-03-19			Newest with 1	2014-08-28	2014-08-28
4	C	2014-03-19
5	D	2014-03-19
6	E	2014-03-19
7	F	2014-08-28
8	A	2014-08-28
9	B	2014-08-28
10	C	2014-08-28
11	D	2014-08-28
12	E	2014-08-28
13	F	2014-08-28
14		2014-08-28
15		2014-08-28
16		2014-08-28
17		2014-08-28
18		2014-08-27
19		2014-08-27
20
Dates

 

[psdileep]

My problem is that data changes every day and I modified given formula to like this: {=MIN(IF(Sheet2!F2:F2000<>"",Sheet2!I2:I2000))}

However, value returned is 1900-01-00

Can you help me with some generic formula and should work even if data varies.

<tbody>
</tbody>

 

[Peter_SSs]

My problem is that data changes every day and I modified given formula to like this: {=MIN(IF(Sheet2!F2:F2000<>"",Sheet2!I2:I2000))}

However, value returned is 1900-01-00

Can you help me with some generic formula and should work even if data varies.

Still confirmed with C+S+E

=MIN(IF(F2:F2000<>"",IF(I2:I2000<>"",I2:I2000)))

 

[psdileep]

Thanks Peter. It worked like a charm.

 

[Peter_SSs]

Thanks Peter. It worked like a charm.

That's good, thanks for letting us know.