A variation on DATEVALUE

  • Thread starter Thread starter Hardy
  • Start date Start date
H

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
 
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
 
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.
 
Back
Top