hi,

i have the folowing problem, i want to find the latest date in a row,
the dates have the format mmm-yy.
the problem is that the MAX function won't work because some of the
dates have apostrophes in it.
the date looks like 04-'02 for example.

I already tried to delete the apostrophes with a Macro, but excel
thinks then that the date format= day-month,
so the actual date then presented with above example is 4 feb, and not
april 2002.

Does anyone know how to solve this problem?

Probably best to convert your "dates" to real dates, then you can use MAX

Assuming the ones formatted as mmm-yy are actually dates (and not text) then
use this formula to convert your non-dates to dates whilst leaving the true
dates alone

=IF(ISTEXT(A1),DATE(100+RIGHT(A1,2),LEFT(A1,2),1), A1)

where your "date" is in A1

"brakbek" wrote:

hi,

i have the folowing problem, i want to find the latest date in a row,
the dates have the format mmm-yy.
the problem is that the MAX function won't work because some of the
dates have apostrophes in it.
the date looks like 04-'02 for example.

I already tried to delete the apostrophes with a Macro, but excel
thinks then that the date format= day-month,
so the actual date then presented with above example is 4 feb, and not
april 2002.

Does anyone know how to solve this problem?

Let's say your dates in column A A2:A100
Create a Helper in column B

B2 =IF(ISNUMBER(A1),A1,DATE(2007,RIGHT(A1,2),LEFT(A1, 2)))
copy from B2 to B100

C2 =MAX(B2:B100)


"brakbek" wrote:

hi,

i have the folowing problem, i want to find the latest date in a row,
the dates have the format mmm-yy.
the problem is that the MAX function won't work because some of the
dates have apostrophes in it.
the date looks like 04-'02 for example.

I already tried to delete the apostrophes with a Macro, but excel
thinks then that the date format= day-month,
so the actual date then presented with above example is 4 feb, and not
april 2002.

Does anyone know how to solve this problem?

Note: my suggestion won't cope with dates before 2000. If you have those
amend to

=IF(ISTEXT(A1),DATE(IF(RIGHT(A1,2)"30",0,100)+RIG HT(A1,2),LEFT(A1,2),1),A1)



"daddylonglegs" wrote:

Probably best to convert your "dates" to real dates, then you can use MAX

Assuming the ones formatted as mmm-yy are actually dates (and not text) then
use this formula to convert your non-dates to dates whilst leaving the true
dates alone

=IF(ISTEXT(A1),DATE(100+RIGHT(A1,2),LEFT(A1,2),1), A1)

where your "date" is in A1

"brakbek" wrote:

hi,

i have the folowing problem, i want to find the latest date in a row,
the dates have the format mmm-yy.
the problem is that the MAX function won't work because some of the
dates have apostrophes in it.
the date looks like 04-'02 for example.

I already tried to delete the apostrophes with a Macro, but excel
thinks then that the date format= day-month,
so the actual date then presented with above example is 4 feb, and not
april 2002.

Does anyone know how to solve this problem?