Date format in a text string
 

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.

Date format in a text string
 

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.

Date format in a text string
 

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

Date format in a text string
 

"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

Date format in a text string
 

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

Date format in a text string
 

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

Date format in a text string
 

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.

I was referring to me as the "'new guy' on the block"... I only started
volunteering in these Excel newsgroups a couple of months ago.

Rick

Date format in a text string
 

Oops! Sorry, Rick....I was a little too quick to jump to Biff's defense.

All is not lost, though.....I got to congratulation him for being awarded MVP.

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

XL2002, WinXP


"Rick Rothstein (MVP - VB)" wrote:

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.

I was referring to me as the "'new guy' on the block"... I only started
volunteering in these Excel newsgroups a couple of months ago.

Rick

Date format in a text string
 

Oops! Sorry, Rick....I was a little too quick to jump to Biff's defense.

No problem. Perhaps I have been posting in these Excel newsgroups long
enough now (about a month or two now) that people here don't think of me as
a "new guy" any more.

All is not lost, though.....I got to congratulation him for being awarded
MVP.

A well worthwhile thing to do. As an MVP from over in the compiled VB world,
I am well aware of the honor that has been bestowed on Biff...
congratulations to him are definitely the order of the day.

Rick

Date format in a text string
 

Thanks to both Rick and Ron.

Ron, I have a feeling that you had something to do with it!

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
Oops! Sorry, Rick....I was a little too quick to jump to Biff's defense.

No problem. Perhaps I have been posting in these Excel newsgroups long
enough now (about a month or two now) that people here don't think of me
as a "new guy" any more.

All is not lost, though.....I got to congratulation him for being awarded
MVP.

A well worthwhile thing to do. As an MVP from over in the compiled VB
world, I am well aware of the honor that has been bestowed on Biff...
congratulations to him are definitely the order of the day.

Rick

Date format in a text string
 

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

Date format in a text string
 

P.S.

For example, you can create this custom format:

GENERAL;GENERAL;GENERAL;[RED]GENERAL

Any TEXT will be displayed in red.

But, if you try using that format in a TEXT function the red text format is
not applied.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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

Date format in a text string
 

"T. Valko" wrote...
....
That's because the TEXT function will only apply number formats.

A1 contains the text abc. B1 contains the formula

=TEXT(A1,"0;-0;\<0\;\-\-@\-\-")

and returns --abc--. At least running Excel 2003 SP1. Which version
are you running? [Surely you would have tested this.]

Date format in a text string
 

"T. Valko" wrote...
....
GENERAL;GENERAL;GENERAL;[RED]GENERAL

Any TEXT will be displayed in red.

But, if you try using that format in a TEXT function the red
text format is not applied.
....

TEXT doesn't apply colors in any of the parts, 0, <0, =0 numbers or
text. General;[Red]General;General would display -1 in red, but in a
cell formatted to display black text,

=TEXT(-1,"General;[Red]General;General")

will display -1 in black text.

Date format in a text string
 

A1 contains the text abc. B1 contains the formula

=TEXT(A1,"0;-0;\<0\;\-\-@\-\-")

and returns --abc--. At least running Excel 2003 SP1.

Ah! Now I see how the fourth section is applied to text in the TEXT
function. Great! Thanks for posting that Harlan. By the way, the dashes do
not appear to be meta-characters within the "text section"... it seems you
do not need the backslashes to escape them there. This works the same as
what you posted...

=TEXT(A1,"0;-0;\<0\;--@--")

Rick

Date format in a text string
 

"Harlan Grove" wrote in message
ps.com...
"T. Valko" wrote...
...
That's because the TEXT function will only apply number formats.

A1 contains the text abc. B1 contains the formula

=TEXT(A1,"0;-0;\<0\;\-\-@\-\-")

and returns --abc--. At least running Excel 2003 SP1. Which version
are you running? [Surely you would have tested this.]


I did, but obviously it wasn't extensive enough.

I stand corrected!

As Rick noted, it works just as well without the slashes: (not extensively
tested)

=TEXT(A1,"0;-0;<0;--@--")

--
Biff
Microsoft Excel MVP