hello,
I have a data base with no date format, just a series of numbers like
20041128 for November 28th year 2004. How can I give this information
a date format?

Assume that value is in A1
in B1

=DATEVALUE(MID(A1,5,2) & "/" & RIGHT(A1,2) & "/" & LEFT(A1,4))

then format the cell with the formula with a date format.

--
Regards,
Tom Ogilvy


"German Velasquez" wrote in message
om...
hello,
I have a data base with no date format, just a series of numbers like
20041128 for November 28th year 2004. How can I give this information
a date format?

If you convert it to a real date, you can format it (via format|cells|number
tab) any way you want.

One way to convert a column of those type values is to select the column,
then Data|text to columns
Choose Fixed width
(remove any line that excel guessed--and don't add any of your own)
Choose a format of ymd
and plop it right back where you picked it up.

German Velasquez wrote:

hello,
I have a data base with no date format, just a series of numbers like
20041128 for November 28th year 2004. How can I give this information
a date format?

--

Dave Peterson

"German Velasquez" wrote:

hello,
I have a data base with no date format, just a series of numbers like
20041128 for November 28th year 2004. How can I give this information
a date format?
There may be a cleaner hack than this. But it will work.

=DATE(LEFT(cell_reference,4),MID(cell_reference,5, 2),RIGHT(cell_reference,2))

Hi Tom, and German,
Why wouldn't you use
=DATE(LEFT(A1,4), MID(A1,5,2), Right(A1,2))
which should be easier on Excel and would be valid
no matter what their Regional settings are for date.

Since my regional settings are usually set to yyyy-mm-dd
your formula would fail for me.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Tom Ogilvy" wrote in message ...
Assume that value is in A1
in B1

=DATEVALUE(MID(A1,5,2) & "/" & RIGHT(A1,2) & "/" & LEFT(A1,4))

then format the cell with the formula with a date format.

--
Regards,
Tom Ogilvy


"German Velasquez" wrote in message
om...
hello,
I have a data base with no date format, just a series of numbers like
20041128 for November 28th year 2004. How can I give this information
a date format?

Hi
and another option
=--TEXT(--A1,"0000-00-00")
and format as date

--
Regards
Frank Kabel
Frankfurt, Germany

"German Velasquez" schrieb im Newsbeitrag
om...
hello,
I have a data base with no date format, just a series of numbers like
20041128 for November 28th year 2004. How can I give this information
a date format?

Frank,
Out of interest, what is the meaning of "--" in Excel formulae ?

NickHK

"Frank Kabel" wrote in message
...
Hi
and another option
=--TEXT(--A1,"0000-00-00")
and format as date

--
Regards
Frank Kabel
Frankfurt, Germany

"German Velasquez" schrieb im Newsbeitrag
om...
hello,
I have a data base with no date format, just a series of numbers like
20041128 for November 28th year 2004. How can I give this information
a date format?

-- converts numbers returned as text or logical values (TRUE/FALSE) into
numerical values, similar to the function VALUE(), but I think the latter
can't convert the logical values.

KL

"NickHK" wrote in message
...
Frank,
Out of interest, what is the meaning of "--" in Excel formulae ?

NickHK

"Frank Kabel" wrote in message
...
Hi
and another option
=--TEXT(--A1,"0000-00-00")
and format as date

--
Regards
Frank Kabel
Frankfurt, Germany

"German Velasquez" schrieb im Newsbeitrag
om...
hello,
I have a data base with no date format, just a series of numbers like
20041128 for November 28th year 2004. How can I give this information
a date format?

It's a thing that Frank does so that people ask what it is :)

But seriously...
It's a double unary operator - it has special uses.

There's a great thread about it - he
http://groups.google.com/groups?hl=e...ing.google.com

And here's some good reading:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"NickHK" wrote in message
...
Frank,
Out of interest, what is the meaning of "--" in Excel formulae ?

NickHK

"Frank Kabel" wrote in message
...
Hi
and another option
=--TEXT(--A1,"0000-00-00")
and format as date

--
Regards
Frank Kabel
Frankfurt, Germany

"German Velasquez" schrieb im Newsbeitrag
om...
hello,
I have a data base with no date format, just a series of numbers like
20041128 for November 28th year 2004. How can I give this information
a date format?

Hi Rob
It's a thing that Frank does so that people ask what it is :)
lol
Good guess from your side but why tell my small secret... ;-)

Regards
Frank

Rob,
The Excel v. VBA handling of booleans seems a bit screwy. Assuming A1=TRUE:

?range("A1").Value=true
True
?-range("A1").Value=true
False
?--range("A1").Value=true
True
?10*range("A1").Value
-10
?10*--range("A1").Value
-10

I can't see the logic for all the above to be valid.

NickHK

"Rob van Gelder" wrote in message
...
It's a thing that Frank does so that people ask what it is :)

But seriously...
It's a double unary operator - it has special uses.

There's a great thread about it - he

http://groups.google.com/groups?hl=e...10528.5bb58 1
37%40posting.google.com

And here's some good reading:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"NickHK" wrote in message
...
Frank,
Out of interest, what is the meaning of "--" in Excel formulae ?

NickHK

"Frank Kabel" wrote in message
...
Hi
and another option
=--TEXT(--A1,"0000-00-00")
and format as date

--
Regards
Frank Kabel
Frankfurt, Germany

"German Velasquez" schrieb im Newsbeitrag
om...
hello,
I have a data base with no date format, just a series of numbers like
20041128 for November 28th year 2004. How can I give this information
a date format?

As in many other languages, in VBA, when coercing numbers to a boolean
value, zero is evaluated as False, any non-zero value will be evaluated
as True.

A True value in VBA will be coerced to -1 in a math operation. In XL,
the coercion is to +1.

In article ,
"NickHK" wrote:

Rob,
The Excel v. VBA handling of booleans seems a bit screwy. Assuming A1=TRUE:

?range("A1").Value=true
True
?-range("A1").Value=true
False
?--range("A1").Value=true
True
?10*range("A1").Value
-10
?10*--range("A1").Value
-10

I can't see the logic for all the above to be valid.