what does the double negative sign do

The TEXT() function returns a *text* string. The double unary "--" coerces
the text number that is the date serial number to a numeric number and then
formatting the cell displays it as a recognizeable date.

Where is this format found in Excel 'numbers tab

This format is not available in the menu FormatCellsNumber. You'd have to
create it in the Custom category.

what does the back slash forward slash mean?

"0000 \ / 00 \ / 00"

The forward slashes are the slashes used as date separators:

1/1/2009

The backslash is a "delimiter". With the number string: 20070703

The format means the first 4 digits are a group followed by a slash, the
next 2 digits are a group followed by a slash and the last 2 digits are a
group.

This is "briefly" mentioned in Excel help under custom number formats.


--
Biff
Microsoft Excel MVP


"yesterdaytoday11" wrote in
message ...
Excellent, this works too but what does the double negative sign do,
reverse
the order? Where is this format found in Excel 'numbers tab and what does
the
back slash forward slash mean?

"T. Valko" wrote:

Try this:

A1 = 20070703

Meaning: yyyy mm dd

=--TEXT(A1,"0000\/00\/00")

Format as Date

--
Biff
Microsoft Excel MVP


"yesterdaytoday11" wrote in
message ...
thanks, this helped alot with our dates formatted like 20070703 for
7/1/2007
with a little editing to
=DATE(LEFT(A9,4),MID(A9,5,2),RIGHT(A9,2)). Now I can use DAYS360 to
compare
with dates in the MM/DD/YYYY format

"T. Valko" wrote:

You can convert the numeric string to a date like this.

the year is one digit in the source data
Assuming the year is *always* in the 2000 decade

A1 = 10501
B1 = 5/1/2001

=DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))

Returns 5/1/2001

So, to comapre the 2 dates:

=DATE(2000+LEFT(A1),MID(A1,2,2),RIGHT(A1,2))=B1

--
Biff
Microsoft Excel MVP


"botany_girl" wrote in message
...
Hello!

I have a worksheet containing two datasets. Each set of data has
associated
dates--but they are entered as different numbers. One set has the
date
in
format ymmdd as a number, i.e. 10501 represents May 1, 2001 (the
year
is
one
digit in the source data). The other set has the date entered as a
date,
i.e. 5/1/2001. I have the columns set up so that they display the
same
numbers, but the original data within the columns is formatted
differently.

I have to look at the data to make sure that the values in the two
columns
match. However, it's a huge dataset, so doing this manually is
going
to
take
a long time. I wanted to insert a logical argument
[=IF(value1=value2,true,false] in a separate column to save time,
but
since
the source data are in different formats, I get a FALSE every time.

Any ideas on how I convert these beasts into identical formats so
that
I
can
use the logical argument strategy? The displayed numbers must not
be
changed
to the odd Excel date-counting format, unfortunately--if that were
the
case
it wouldn't be so difficult.

Thanks for any suggestions,

lisa