find latest date in a row, when dates have apostrophes in it
 

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?

find latest date in a row, when dates have apostrophes in it
 

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?

find latest date in a row, when dates have apostrophes in it
 

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?

find latest date in a row, when dates have apostrophes in it
 

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?