query about VALUE function

gvm wrote...
....
>My question: If the contents of the cell are formatted to be any of constant
>number, date or time formats as specified by the HELP info, then what defines
>the contents to be text in the first place?

You're pointing out an ambiguous use of the word 'format' or
'formatting' that's immediately obvious to programmers but not to
nonprogrammers. The argument to VALUE is *always* considered to be
text. If that text happens to be the same as the output/displayed
representation of some numeric value in some accepted number format,
then VALUE returns that numeric value.

This reply is just text. And the next nonblank line,

1,234,567.89

is just text. It looks like a formatted number, and VALUE would convert
it into the numeric value 1234567.89. Likewise, the following Excel
formula in cell A2,

="1,234,567.89"

would just be text in Excel even though it looks like a number.

In Excel, cell values are text if they're text constants (which I'll
define lazily as entries that aren't formulas and that Excel doesn't
interpret as numeric, boolean or error values) and formulas involving
text strings (anything within delimiting double quotes) and formulas
that return text values. Such text values may *appear* the same as
numeric, date or time values, but their appearance is *IRRELEVANT*.

When online help for the VALUE function says text formatted as numbers,
dates or times, it means characters *AS* *TEXT* that appear the same as
numeric, date or time values in some accepted number formats. Another
example, this time a date.

A1:
1

B2:
Jan

C3:
2006

D4:
=A1&"-"&B2&"-"&C3

Cell D4 will appear as 1-Jan-2006, which looks like a date value, but
it'd actually be a text value that just happens to look like a
formatted date value. It's *NOT* a date value. While many Excel
functions would treat D4 as a date value (e.g., DAY, MONTH, YEAR,
DATEDIF), the fundamental functions COUNT and ISNUMBER won't -
COUNT(D4) would return 0 and ISNUMBER(D4) would return FALSE. However,
ISNUMBER(VALUE(D4)) would return TRUE.

gvm wrote...
....
>My question: If the contents of the cell are formatted to be any of constant
>number, date or time formats as specified by the HELP info, then what defines
>the contents to be text in the first place?

You're pointing out an ambiguous use of the word 'format' or
'formatting' that's immediately obvious to programmers but not to
nonprogrammers. The argument to VALUE is *always* considered to be
text. If that text happens to be the same as the output/displayed
representation of some numeric value in some accepted number format,
then VALUE returns that numeric value.

This reply is just text. And the next nonblank line,

1,234,567.89

is just text. It looks like a formatted number, and VALUE would convert
it into the numeric value 1234567.89. Likewise, the following Excel
formula in cell A2,

="1,234,567.89"

would just be text in Excel even though it looks like a number.

In Excel, cell values are text if they're text constants (which I'll
define lazily as entries that aren't formulas and that Excel doesn't
interpret as numeric, boolean or error values) and formulas involving
text strings (anything within delimiting double quotes) and formulas
that return text values. Such text values may *appear* the same as
numeric, date or time values, but their appearance is *IRRELEVANT*.

When online help for the VALUE function says text formatted as numbers,
dates or times, it means characters *AS* *TEXT* that appear the same as
numeric, date or time values in some accepted number formats. Another
example, this time a date.

A1:
1

B2:
Jan

C3:
2006

D4:
=A1&"-"&B2&"-"&C3

Cell D4 will appear as 1-Jan-2006, which looks like a date value, but
it'd actually be a text value that just happens to look like a
formatted date value. It's *NOT* a date value. While many Excel
functions would treat D4 as a date value (e.g., DAY, MONTH, YEAR,
DATEDIF), the fundamental functions COUNT and ISNUMBER won't -
COUNT(D4) would return 0 and ISNUMBER(D4) would return FALSE. However,
ISNUMBER(VALUE(D4)) would return TRUE.

The HELP info on the VALUE function includes the statements:
------------------------------------------
VALUE(text)
Text is the text enclosed in quotation marks or a reference to a cell
containing the text you want to convert.
Remarks
Text can be in any of the constant number, date, or time formats recognized
by Microsoft Excel. If text is not in one of these formats, VALUE returns the
#VALUE! error value.
-----------------------------------------
My question: If the contents of the cell are formatted to be any of constant
number, date or time formats as specified by the HELP info, then what defines
the contents to be text in the first place?
--

gvm wrote...
....
>My question: If the contents of the cell are formatted to be any of constant
>number, date or time formats as specified by the HELP info, then what defines
>the contents to be text in the first place?

You're pointing out an ambiguous use of the word 'format' or
'formatting' that's immediately obvious to programmers but not to
nonprogrammers. The argument to VALUE is *always* considered to be
text. If that text happens to be the same as the output/displayed
representation of some numeric value in some accepted number format,
then VALUE returns that numeric value.

This reply is just text. And the next nonblank line,

1,234,567.89

is just text. It looks like a formatted number, and VALUE would convert
it into the numeric value 1234567.89. Likewise, the following Excel
formula in cell A2,

="1,234,567.89"

would just be text in Excel even though it looks like a number.

In Excel, cell values are text if they're text constants (which I'll
define lazily as entries that aren't formulas and that Excel doesn't
interpret as numeric, boolean or error values) and formulas involving
text strings (anything within delimiting double quotes) and formulas
that return text values. Such text values may *appear* the same as
numeric, date or time values, but their appearance is *IRRELEVANT*.

When online help for the VALUE function says text formatted as numbers,
dates or times, it means characters *AS* *TEXT* that appear the same as
numeric, date or time values in some accepted number formats. Another
example, this time a date.

A1:
1

B2:
Jan

C3:
2006

D4:
=A1&"-"&B2&"-"&C3

Cell D4 will appear as 1-Jan-2006, which looks like a date value, but
it'd actually be a text value that just happens to look like a
formatted date value. It's *NOT* a date value. While many Excel
functions would treat D4 as a date value (e.g., DAY, MONTH, YEAR,
DATEDIF), the fundamental functions COUNT and ISNUMBER won't -
COUNT(D4) would return 0 and ISNUMBER(D4) would return FALSE. However,
ISNUMBER(VALUE(D4)) would return TRUE.