datevalue

[Guest]

When I try to use the function DateValue, I keep getting an error message of
"wrong data type". I've tried many ways of entering the date: '12/31/05,
'12/31/2005, "12/31/2005", but none seem to work.

This was okay last year, but recently I get the error messages. Can someone
help?

 

[Govind]

Hi,

Try

=DATEVALUE("31-Dec-2005")

It works.

Your first two values were within single quotes and hence they wont
work. On the third format, am not sure of the reason why it doesnt work,
but your date format should be in sync with your default date format set
in the Regional settings.

Regards

Govind.

 

[Biff]

Hi!

The WORKSHEET function DATEVALUE doesn't return that type of error.

Is this in some code?

Either way, you'll need to provide more details.

Biff

 

[Guest]

Hi!

The WORKSHEET function DATEVALUE doesn't return that type of error.

Is this in some code?

Either way, you'll need to provide more details.

Biff




Hey Biff,

Well my spreadsheet returns this error! And it isn't CODE that I know of.
I've tried entering all sorts of date formats and I still get the error
message: ....wrong data type.

I've set the column to "date" with mm/dd/yyyy as the format. I've input
dates using a leading apostrophy ('01/01/2005) which displays as a date and
doesn't "calculate" the division a "/" calls for). I've input dates using
straight 7/21/5 and had 07/21/2005 displayed because of the column format.
Also, used an input of
"07/21/2005". Even with the quotes, it doesn't work.

All of these give me a #Value! response in the cell and an explanation of
"...wrong data type."

I use this function in a calculation, but no more. Can't get the difference
between two dates to do an interest calc.

LarrryTheK

 

[Guest]

Hi,

Try

=DATEVALUE("31-Dec-2005")

It works.

Your first two values were within single quotes and hence they wont
work. On the third format, am not sure of the reason why it doesnt work,
but your date format should be in sync with your default date format set
in the Regional settings.

Regards

Govind.

Sorry, it didn't help. Double quotes still give give the same error
message: Wrong data type.

I think the function is wrong.

Bottom line, I can't compute date differences to use in a formula.

LarryTheK

 

[Piranha]

LarryTheK,

If i copy and paste the =DATEVALUE("31-Dec-2005") as Govind
provided it works fine. it shows the date as a serial.

If you Format a cell like you said (mm/dd/yyyy) (say A1). then if you
enter the date with a
leading ' you are takeing away the date format and entering the date as
text.

If you do this and your text/date is in A1 and you put in B1
=DATEVALUE(A1)
then you will also get the serial.

If you do the above and A1 is without the leading ' you will get the
#VALUE! error.

Do you have any data validation causing the (wrong data type) message?

It sound like you are using this data in some kind of caluction, which
may also
have some bearing.

Dave

Well my spreadsheet returns this error! And it isn't CODE that I know
of.
I've tried entering all sorts of date formats and I still get the
error
message: ....wrong data type.

I've set the column to "date" with mm/dd/yyyy as the format. I've
input
dates using a leading apostrophy ('01/01/2005) which displays as a date
and
doesn't "calculate" the division a "/" calls for). I've input dates
using
straight 7/21/5 and had 07/21/2005 displayed because of the column
format.
Also, used an input of
"07/21/2005". Even with the quotes, it doesn't work.

All of these give me a #Value! response in the cell and an explanation
of
"...wrong data type."

I use this function in a calculation, but no more. Can't get the
difference
between two dates to do an interest calc.

LarrryTheK

Govind
Guest Posts: n/a

Re: datevalue

--------------------------------------------------------------------------------

Hi,

Try

=DATEVALUE("31-Dec-2005")

It works.

 

[Guest]

LarryTheK,

If i copy and paste the =DATEVALUE("31-Dec-2005") as Govind
provided it works fine. it shows the date as a serial.

If you Format a cell like you said (mm/dd/yyyy) (say A1). then if you
enter the date with a
leading ' you are takeing away the date format and entering the date as
text.

If you do this and your text/date is in A1 and you put in B1
=DATEVALUE(A1)
then you will also get the serial.

If you do the above and A1 is without the leading ' you will get the
#VALUE! error.

Do you have any data validation causing the (wrong data type) message?

It sound like you are using this data in some kind of caluction, which
may also
have some bearing.

Dave

Govind
Guest Posts: n/a

Re: datevalue

--------------------------------------------------------------------------------

Hi,

Try

=DATEVALUE("31-Dec-2005")

It works.

04/04/2005
05/05/2005
#VALUE!

This is what I have. Dates are entered with a date format for this display.
The third cell uses the datevalue function to subtract the second from the
first. It is used in an interest calculating spreadsheet.

Why do I get the error? Do I have a corrupt excel program?

LarryTheK

 

[Guest]

LarryTheK,

If i copy and paste the =DATEVALUE("31-Dec-2005") as Govind
provided it works fine. it shows the date as a serial.

If you Format a cell like you said (mm/dd/yyyy) (say A1). then if you
enter the date with a
leading ' you are takeing away the date format and entering the date as
text.

If you do this and your text/date is in A1 and you put in B1
=DATEVALUE(A1)
then you will also get the serial.

If you do the above and A1 is without the leading ' you will get the
#VALUE! error.

Do you have any data validation causing the (wrong data type) message?

It sound like you are using this data in some kind of caluction, which
may also
have some bearing.

Dave

Govind
Guest Posts: n/a

Re: datevalue

--------------------------------------------------------------------------------

Hi,

Try

=DATEVALUE("31-Dec-2005")

It works.


--
Piranha
------------------------------------------------------------------------
Piranha's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20435
View this thread: http://www.excelforum.com/showthread.php?threadid=387908

A further question: why do I get > when I seem to use " ???

 

[Piranha]

04/04/2005
05/05/2005
#VALUE!

This is what I have. Dates are entered with a date format for this
display.
The third cell uses the datevalue function to subtract the second from
the
first. It is used in an interest calculating spreadsheet.

Why do I get the error? Do I have a corrupt excel program?

LarryTheK

LarrryTheK,
You can't use the DateValue function with a date formated cell. It has
to be text.
The objective of the DateValue function is to convert a date as text
into a serial.

IE
A1 & A2 formated as text (leading')
B1 & B2 formated as general
A1.......................B1
4/4/2005............38446
5/5/2005............38477
...........................31
So A1 Formated as text (leading ')with the date '4/4/2005
B1 formated as general with =DATEVALUE(A1) will give serial 38446

So A2 Formated as text (leading ')with the date '5/5/2005
B2 formated as general with =DATEVALUE(A2) will give serial 38477

B3 is formated as general with the formula =B2-B1
Gives the result of 31 days

This help?
Dave