I have a date in A1 and need to reference it in a line of text. So, in cell
A2, I used the string '="This figure is from " & a1'. But, the result looks
like 'This figure is from 39230'.

How do I write the formula so it would read the date as 5/28/07 instead? I
tried formatting the cell as a date cell, but that didn't work.

Try this:

="This figure is from " &IF(A1="","---",TEXT(A1,"m/dd/yy"))

If A1 is empty the formula will return:

This figure is from ---

--
Biff
Microsoft Excel MVP


"osbornauto" wrote in message
...
I have a date in A1 and need to reference it in a line of text. So, in
cell
A2, I used the string '="This figure is from " & a1'. But, the result
looks
like 'This figure is from 39230'.

How do I write the formula so it would read the date as 5/28/07 instead?
I
tried formatting the cell as a date cell, but that didn't work.

Try this:

="This figure is from " &IF(A1="","---",TEXT(A1,"m/dd/yy"))

If A1 is empty the formula will return:

This figure is from ---

You don't really need the IF test...

="This figure is from "&TEXT(A1,"m/dd/yy;;---")

Rick

"Rick Rothstein (MVP - VB)" wrote in
message ...
Try this:

="This figure is from " &IF(A1="","---",TEXT(A1,"m/dd/yy"))

If A1 is empty the formula will return:

This figure is from ---

You don't really need the IF test...

="This figure is from "&TEXT(A1,"m/dd/yy;;---")

Rick

See, you learn something new everyday!

I didn't know you could include multiple format styles in the TEXT function.

For those who may wonder what that means:

A cells format is divided into 4 categories: positive numbers, negative
numbers, 0, and text. These categories are separated by a semicolon ;.

In Rick's example: m/dd/yy;;---

m/dd/yy is the desired format for positive numbers (in Excel a date is
really just a formatted number)
The format for negative numbers is empty (which means a negative number will
not be displayed)
--- is the desired format for 0
The text category has been ommited and will display any text in the default
manner.

So, if A1 is empty the cell evaluates to 0 and the displayed format style
for 0 has been defined to be --- so the result of the formula will be:

This figure is from ---

--
Biff
Microsoft Excel MVP

You don't really need the IF test...

="This figure is from "&TEXT(A1,"m/dd/yy;;---")

See, you learn something new everyday!

Look at that... the "new guy" on the block had something new to show one of
the regulars.<g


I didn't know you could include multiple format styles in the TEXT
function.

Interesting... I just looked and see that this feature is not mentioned in
the help files for the TEXT function. I find that odd. Anyway, as I have
mentioned before, I am returning to Excel after a very lengthy absence
(10-15 years), so I find I am relearning a lot and coming across lots of new
things; but more importantly, I am not constrained by what everyone else
"knows to be fact". In the case of the TEXT function, it appeared to me to
be a spreadsheet function equivalent to the compiled VB (where I spent most
of my previous 15 years) and/or VBA Format function. I figured that was
confirmed by the constructions possible in Custom Formatting figuring the
same underlying function calls were underneath it all. So, never looking at
the documentation, I just figured that same syntax could be applied; hence,
my matter-of-fact posting in this thread. A quick experiment, though, shows
differences between the TEXT function and Format function's implementation
of this... in the Format function, do this Format(Value,"#;;z\ero") and
negative numbers print out as expected (they adopt the formatting from the
first category section as if the no alternate category sections were used)
whereas doing this TEXT(Value,"#;;z\ero") prints out nothing for negative
numbers (if a category section is shown in the TEXT function, it is used).

For those who may wonder what that means:

A cells format is divided into 4 categories: positive numbers, negative
numbers, 0, and text.

I am not so sure of that fourth category. In the compiled VB world, the
fourth category for the Format function is returned for a NULL value in the
first argument. This also applies to the VBA world. For example, enter these
two lines into the Immediate window in Excel's VBA IDE...

Rick=NULL
? Format(Rick,"#;#;z\ero;Oh No")

and Oh No will print out. I had trouble getting anything to work in the TEXT
function when I tried to make use of the fourth category section. I wonder
if any of this TEXT function stuff is documented?


Biff
Microsoft Excel MVP

I see something new in your signature.... Congratulation!


Rick

Look at that... the "new guy" on the block had something new to show one of
the regulars.<g

Rick...That "new guy" is one of the veterans and the change to his signature
(which I just noticed) was WAAAaaay overdue.

Congratulations, Biff!

***********
Regards,
Ron

XL2002, WinXP


"Rick Rothstein (MVP - VB)" wrote:

You don't really need the IF test...

="This figure is from "&TEXT(A1,"m/dd/yy;;---")

See, you learn something new everyday!

Look at that... the "new guy" on the block had something new to show one of
the regulars.<g


I didn't know you could include multiple format styles in the TEXT
function.

Interesting... I just looked and see that this feature is not mentioned in
the help files for the TEXT function. I find that odd. Anyway, as I have
mentioned before, I am returning to Excel after a very lengthy absence
(10-15 years), so I find I am relearning a lot and coming across lots of new
things; but more importantly, I am not constrained by what everyone else
"knows to be fact". In the case of the TEXT function, it appeared to me to
be a spreadsheet function equivalent to the compiled VB (where I spent most
of my previous 15 years) and/or VBA Format function. I figured that was
confirmed by the constructions possible in Custom Formatting figuring the
same underlying function calls were underneath it all. So, never looking at
the documentation, I just figured that same syntax could be applied; hence,
my matter-of-fact posting in this thread. A quick experiment, though, shows
differences between the TEXT function and Format function's implementation
of this... in the Format function, do this Format(Value,"#;;z\ero") and
negative numbers print out as expected (they adopt the formatting from the
first category section as if the no alternate category sections were used)
whereas doing this TEXT(Value,"#;;z\ero") prints out nothing for negative
numbers (if a category section is shown in the TEXT function, it is used).

For those who may wonder what that means:

A cells format is divided into 4 categories: positive numbers, negative
numbers, 0, and text.

I am not so sure of that fourth category. In the compiled VB world, the
fourth category for the Format function is returned for a NULL value in the
first argument. This also applies to the VBA world. For example, enter these
two lines into the Immediate window in Excel's VBA IDE...

Rick=NULL
? Format(Rick,"#;#;z\ero;Oh No")

and Oh No will print out. I had trouble getting anything to work in the TEXT
function when I tried to make use of the fourth category section. I wonder
if any of this TEXT function stuff is documented?


Biff
Microsoft Excel MVP

I see something new in your signature.... Congratulation!


Rick

I had trouble getting anything to work in the TEXT function when I tried to
make use of the fourth category section.

That's because the TEXT function will only apply number formats.

When I was explaining the 4 categories it was in a general sense, not
exclusive to use in the TEXT function.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
You don't really need the IF test...

="This figure is from "&TEXT(A1,"m/dd/yy;;---")

See, you learn something new everyday!

Look at that... the "new guy" on the block had something new to show one
of the regulars.<g


I didn't know you could include multiple format styles in the TEXT
function.

Interesting... I just looked and see that this feature is not mentioned in
the help files for the TEXT function. I find that odd. Anyway, as I have
mentioned before, I am returning to Excel after a very lengthy absence
(10-15 years), so I find I am relearning a lot and coming across lots of
new things; but more importantly, I am not constrained by what everyone
else "knows to be fact". In the case of the TEXT function, it appeared to
me to be a spreadsheet function equivalent to the compiled VB (where I
spent most of my previous 15 years) and/or VBA Format function. I figured
that was confirmed by the constructions possible in Custom Formatting
figuring the same underlying function calls were underneath it all. So,
never looking at the documentation, I just figured that same syntax could
be applied; hence, my matter-of-fact posting in this thread. A quick
experiment, though, shows differences between the TEXT function and Format
function's implementation of this... in the Format function, do this
Format(Value,"#;;z\ero") and negative numbers print out as expected (they
adopt the formatting from the first category section as if the no
alternate category sections were used) whereas doing this
TEXT(Value,"#;;z\ero") prints out nothing for negative numbers (if a
category section is shown in the TEXT function, it is used).

For those who may wonder what that means:

A cells format is divided into 4 categories: positive numbers, negative
numbers, 0, and text.

I am not so sure of that fourth category. In the compiled VB world, the
fourth category for the Format function is returned for a NULL value in
the first argument. This also applies to the VBA world. For example, enter
these two lines into the Immediate window in Excel's VBA IDE...

Rick=NULL
? Format(Rick,"#;#;z\ero;Oh No")

and Oh No will print out. I had trouble getting anything to work in the
TEXT function when I tried to make use of the fourth category section. I
wonder if any of this TEXT function stuff is documented?


Biff
Microsoft Excel MVP

I see something new in your signature.... Congratulation!


Rick