A variation on DATEVALUE

[Hardy]

Hi,

I've searched other posts on this, but this is a varient and I am a bi
stuck.

I have various dates from current to 01-Aug-34 - they are bond maturit
dates (Col A) . Data is downloaded into spreadsheet in this format.

I need to simply convert to excel format. Using DATEVALE(A2) in cel
B2 works fine until you get to dates of 2030 and beyond, then Exce
assumes 19xx instead of 20xx. So, 01-Aug-34 comes out at excel dat
12632, rather than 49157.

Manually editing A2 to 01-Aug-2034 returns #VALUE! om B2. Doin
DATEVALUE(""&A2&"") produces same (I was getting desperate!). Doin
DATEVALUE("01-Aug-2034") works fine (49157), but does not solve for m
as I need cell reference in formula

 

[Niek Otten]

For Windows 2000 and higher, you can change the interpretation of 2-digit
years in the Control panel, regional options.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Hardy]

Niek,

Hmmm, did that but did not solve my problem, even after I re-booted.

Still, manged to find workaround with help from colleague. although
will have 'new century bug', but I'll be long gone.

In B2, I put; DATEVALUE(LEFT((A2,7) & "20" & RIGHT(A2,2))

Then seemed to behave.