Problem with DATEVALUE

[John Woodgate]

I'm new here, so if I put a foot wrong, please be lenient.

Computer: Dell Dimension 8300

Operating System: Windows XP Home

Error Text: #VALUE!

Problem Description: The problem is with Excel 2003. Microsoft's own
example for the DATEVALUE function, DATEVALUE ("8/22/2008") produces the
error message #VALUE! The other examples, with the month in alphabetic
characters, do not produce the error. I've tried other formats, such as
8-22-2008 and 22-8-2008 (since I am using English (UK) dates), with the
same result.

I've tried many ways to try to find out why this happens, without
success.

I have a worksheet with 1600 dates in the form '1-10-1997' ('dd-mm-
yyyy') and I need to convert them to ISO format '1997-10-01' (yyyy-mm-
dd), with leading zeros on numbers less than 10.

I have found elsewhere the 'text-to-columns' procedure to split the
original data into three temporary columns (formatted as text) and
reconstitute as dates in a fourth column (formatted as date 'yyyy-mm-
dd') but I can't find a way of adding the leading zeroes, so that
'1-1-1997' reappears as '1997-1-1' at present.

 

[Frank Kabel]

Hi
formula works for me. Maybe check your Windows regional
settings what you're using as date delimiter.

Or use
=DATE(2004,8,22)

 

[John Woodgate]

I read in microsoft.public.excel.worksheet.functions that Frank Kabel

Hi
formula works for me.

Thanks for prompt reply. No doubt, but it doesn't work for me.

Maybe check your Windows regional
settings what you're using as date delimiter.

I thought I'd covered this. I tried Microsoft's example 8/22/2008 with
cell format date set to US, and the error occurred. Then I tried
8-22-2008 and 22-8-2008 with cell format date set to UK, and the error
occurred for these as well.

If I could solve the addition of leading zeroes to single-digit days and
months, the 'text-to-column' method would work, but it's less elegant
than a simple re-formatting.

Or use
=DATE(2004,8,22)

Even if that worked, I don't see how to use it for my 1600 dates that
are not in that format.

 

[Frank Kabel]

Hi
how do you get your data values. Are they imported. If yes what format
do you have 8sounds for me they're stored as 'Text')

 

[John Woodgate]

I read in microsoft.public.excel.worksheet.functions that Frank Kabel

how do you get your data values. Are they imported.

Imported by someone in another country from an Access database. The
cells are not shown as having any format in the file as I received it.

If yes what format
do you have 8sounds for me they're stored as 'Text')

I don't have 8 sounds for you. (;-)

 

[hgrove]

John Woodgate wrote...

. . . Frank Kabel wrote...

I thought I'd covered this. I tried Microsoft's example 8/22/2008
with cell format date set to US, and the error occurred. Then I
tried 8-22-2008 and 22-8-2008 with cell format date set to UK,
and the error occurred for these as well.

...

A cell's number format has no bearing on what DATEVALUE will or won'
accept. On my system with standard US date settings, I get th
following results - col 1 are all text, col 2 are all correspondin
DATEVALUE results as date serial numbers formatted as General.

7/21/2004 38189
21/7/2004 #VALUE!
2004-07-21 38189
July 21, 2004 38189
21 July 2004 38189
21-Jul-2004 38189
7-21-2004 38189
21-7-2004 #VALUE!

Then I format the second column as mm/dd/yyyy and I get

7/21/2004 07/21/2004
21/7/2004 #VALUE!
2004-07-21 07/21/2004
July 21, 2004 07/21/2004
21 July 2004 07/21/2004
21-Jul-2004 07/21/2004
7-21-2004 07/21/2004
21-7-2004 #VALUE!

However, when I switch to standard UK date settings with the secon
column formatted General, I get

7/21/2004 #VALUE!
21/7/2004 38189
2004-07-21 38189
July 21, 2004 #VALUE!
21 July 2004 38189
21-Jul-2004 38189
7-21-2004 #VALUE!
21-7-2004 38189

and changing the second column's format to dd/mm/yyyy gives

7/21/2004 #VALUE!
21/7/2004 21/07/2004
2004-07-21 21/07/2004
July 21, 2004 #VALUE!
21 July 2004 21/07/2004
21-Jul-2004 21/07/2004
7-21-2004 #VALUE!
21-7-2004 21/07/2004


So it sure looks like Frank was correct in his diagnosis of you
problem. You may need to change your regional settings using th
Regional Settings applet in Windows's Control Panel

 

[Frank Kabel]

Hi
o.k. try the following directly after importing the data:
- select an empty cell
- copy this cell
- select your imported data values
- goto 'Edit - Paste Special' and choose 'Add'

Now try changing the format again.

 

[John Woodgate]

I read in microsoft.public.excel.worksheet.functions that Frank Kabel

o.k. try the following directly after importing the data:
- select an empty cell
- copy this cell
- select your imported data values
- goto 'Edit - Paste Special' and choose 'Add'

Now try changing the format again.

That doesn't do anything, I afraid. The cells stay in the '1-10-1997'
format, and DATEVALUE() returns #VALUE!.

 

[John Woodgate]

I read in microsoft.public.excel.worksheet.functions that hgrove

However, when I switch to standard UK date settings with the second

column formatted General, I get

7/21/2004 #VALUE!
21/7/2004 38189
2004-07-21 38189
July 21, 2004 #VALUE!
21 July 2004 38189
21-Jul-2004 38189
7-21-2004 #VALUE!
21-7-2004 38189

That last one is the one that simply doesn't work on my machine. if it
did, I could convert the serial number back to ISO format yyyy-mm-dd,
with leading zeroes.

and changing the second column's format to dd/mm/yyyy gives

I don't want to do that, so I've snipped to save bandwidth.

So it sure looks like Frank was correct in his diagnosis of your

problem. You may need to change your regional settings using the

Regional Settings applet in Windows's Control Panel.

That is set to UK, and the format of my dates is like '1-10-1997' which
is a UK format recognized by Excel. So I don't see this as a problem of
regional settings, at present.

 

[Frank Kabel]

Hi
then see Harlan's post. Check your regional settings!

 

[hgrove]

John Woodgate wrote...
...

That doesn't do anything, I afraid. The cells stay in the
'1-10-1997' format, and DATEVALUE() returns #VALUE!.

If DATEVALUE("1-10-1997") returns #VALUE!, then you have a big problem
This particular date is ambiguous (either 1 Oct or Jan 10), but it'
valid under both US and UK date settings. Since you've writte
'1-10-1997' in single quotes, are there actually single quotes in thes
cells? If so, they'll screw up DATEVALUE. If not, have you checke
whether there might be stray nonbreaking space characters in thes
cells

 

[Frank Kabel]

[....]

That is set to UK, and the format of my dates is like '1-10-1997' which
is a UK format recognized by Excel. So I don't see this as a problem of
regional settings, at present.

And as Harlan's example showed this is the problem
Try changing your regional settings (NOT the cell format) to something
like
MM/DD/YYYY

and try again. Your UK regional settings are different than your date
values.

Just to give you an alternative formula if your date value is in cell
A1. instead of
=DATEVALUE(A1)

try
=DATE(MID(A1,FIND("/",A1,4)+1,4),LEFT(A1,FIND("/",A1)-1),MID(A1,FIND("/
",A1)+1,2))

Frank

 

[John Woodgate]

I read in microsoft.public.excel.worksheet.functions that hgrove

If DATEVALUE("1-10-1997") returns #VALUE!, then you have a big problem.
This particular date is ambiguous (either 1 Oct or Jan 10), but it's
valid under both US and UK date settings. Since you've written
'1-10-1997' in single quotes, are there actually single quotes in these
cells? If so, they'll screw up DATEVALUE. If not, have you checked
whether there might be stray nonbreaking space characters in these
cells?

There are no single quotes in the cells and there are no hidden
characters. Regional setting in Control Panel is UK and the same in
Format Cells>Number>Date.

I opened a new workbook and tried some variations. Here are three
columns (probably best viewed in a monospaced font):
A B C
10-10-1997 #VALUE! Date
2001-10-11 #VALUE! Date
10/11/2001 #VALUE! General
10-11-2001 #VALUE! Text
2001-04-07 #VALUE! General
1-10-1997 #VALUE! General
2002-08-06 37474 Text

A is what I entered [1], without preformatting the column, and C is what
Excel thinks the data are. B is the result of DATEVALUE(A*).

Now if I format column A as text, I get:

A B C
10-10-1997 #VALUE! Text
37175 #VALUE! Text
10/11/2001 #VALUE! Text
10-11-2001 #VALUE! Text
36988 #VALUE! Text
1-10-1997 #VALUE! Text
2002-08-06 37474 Text

I think it's 'interesting' that formatting the column as text converts
the two dates to serial dates! However, that still doesn't explain why
Excel balks at 10-10-1997 and 10/11/2001, which Microsoft claims it will
handle in DATEVALUE().

[1] Well, not quite. I tried to enter 2001-4-7, but Excel inserted the
leading zeroes.

 

[John Woodgate]

I read in microsoft.public.excel.worksheet.functions that Frank Kabel

And as Harlan's example showed this is the problem Try changing your
regional settings (NOT the cell format) to something
like
MM/DD/YYYY

Misunderstanding. Regional settings are 'UK' or 'US'. MM/DD/YYYY is a
date format, and a US one at that. But I already tried all those things
before asking here.

and try again. Your UK regional settings are different than your date
values.

No. I haven't previously said which date format I have set in Format
Cells > Number > date, but I have settled on dd-mm-yyyy, to match
1-10-1997.

Just to give you an alternative formula if your date value is in cell
A1. instead of
=DATEVALUE(A1)

try
=DATE(MID(A1,FIND("/",A1,4)+1,4),LEFT(A1,FIND("/",A1)-1),MID(A1,FIND("/
",A1)+1,2))

I'll try that (with suitable amendments regarding '/', which is not in
my dates), but tomorrow. It's too late even for me, now. Thank you.

Is there a way of adding a leading zero to a single digit in a cell? If
so, I can fall back on the 'text-to-columns' solution, which at least
doesn't give error messages but gives 1997-8-1 instead of 1997-08-01.

 

[Frank Kabel]

Hi
if the values in column are aLREADY stored as dates, DATEVALUE won't
work. Datevalue expects a text value.

But if you like just email me your current file and i'll have a look at
it
email: frank[dot]kabel[at]freeenet[dot]de

--
Regards
Frank Kabel
Frankfurt, Germany

I read in microsoft.public.excel.worksheet.functions that hgrove

If DATEVALUE("1-10-1997") returns #VALUE!, then you have a big problem.
This particular date is ambiguous (either 1 Oct or Jan 10), but it's
valid under both US and UK date settings. Since you've written
'1-10-1997' in single quotes, are there actually single quotes in these
cells? If so, they'll screw up DATEVALUE. If not, have you checked
whether there might be stray nonbreaking space characters in these
cells?

There are no single quotes in the cells and there are no hidden
characters. Regional setting in Control Panel is UK and the same in
Format Cells>Number>Date.

I opened a new workbook and tried some variations. Here are three
columns (probably best viewed in a monospaced font):
A B C
10-10-1997 #VALUE! Date
2001-10-11 #VALUE! Date
10/11/2001 #VALUE! General
10-11-2001 #VALUE! Text
2001-04-07 #VALUE! General
1-10-1997 #VALUE! General
2002-08-06 37474 Text

A is what I entered [1], without preformatting the column, and C is what
Excel thinks the data are. B is the result of DATEVALUE(A*).

Now if I format column A as text, I get:

A B C
10-10-1997 #VALUE! Text
37175 #VALUE! Text
10/11/2001 #VALUE! Text
10-11-2001 #VALUE! Text
36988 #VALUE! Text
1-10-1997 #VALUE! Text
2002-08-06 37474 Text

I think it's 'interesting' that formatting the column as text converts
the two dates to serial dates! However, that still doesn't explain why
Excel balks at 10-10-1997 and 10/11/2001, which Microsoft claims it will
handle in DATEVALUE().

[1] Well, not quite. I tried to enter 2001-4-7, but Excel inserted the
leading zeroes.
--
Regards, John Woodgate, OOO - Own Opinions Only.
The good news is that nothing is compulsory.
The bad news is that everything is prohibited.
http://www.jmwa.demon.co.uk Also see http://www.isce.org.uk

 

[John Woodgate]

I read in microsoft.public.excel.worksheet.functions that Frank Kabel

) about 'Problem with DATEVALUE', on Thu, 22 Jul 2004:
if the values in column are aLREADY stored as dates, DATEVALUE won't
work. Datevalue expects a text value.

I know. The trouble is I don't know the key fact that solves the
problem.

But if you like just email me your current file and i'll have a look at
it
email: frank[dot]kabel[at]freeenet[dot]de

That's a very generous offer. Thank you. Tomorrow I will send it. But I
fear that it will work on your machine but not on mine. (8-O(

 

[John Woodgate]

I read in microsoft.public.excel.worksheet.functions that John Woodgate

I read in microsoft.public.excel.worksheet.functions that Frank Kabel

) about 'Problem with DATEVALUE', on Thu, 22 Jul 2004:
if the values in column are aLREADY stored as dates, DATEVALUE won't
work. Datevalue expects a text value.

I know. The trouble is I don't know the key fact that solves the
problem.

But if you like just email me your current file and i'll have a look at
it
email: frank[dot]kabel[at]freeenet[dot]de

That's a very generous offer. Thank you. Tomorrow I will send it. But I
fear that it will work on your machine but not on mine. (8-O(

I want to thank Frank Kabel here for providing a work-around solution:

=DATE(RIGHT(C2,4),MID(C2,FIND("-",C2)+1,FIND("-",C2,FIND("-",C2)+1)-FIND
("-"
,C2)-1),LEFT(C2,FIND("-",C2)-1))

C2 needs to be in Text format. This converts 1-4-1997 to (I believe) any
date format showing year, month and day, but particularly 1997-04-01,
which is what I want.