yyyymmdd date format

[German Velasquez]

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?

 

[Tom Ogilvy]

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.

 

[Dave Peterson]

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.

 

[Guest]

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))

 

[David McRitchie]

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.

 

[Frank Kabel]

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

 

[NickHK]

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

NickHK

 

[KL]

-- 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

 

[Rob van Gelder]

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 - here:
http://groups.google.com/groups?hl=en&lr=&[email protected]

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

 

[Frank Kabel]

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

 

[NickHK]

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

 

[JE McGimpsey]

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.