Date format in a text string

[Guest]

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.

 

[T. Valko]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[T. Valko]

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

 

[Rick Rothstein \(MVP - VB\)]

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

See, you learn something new everyday!

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

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

 

[Guest]

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

Look at that... the "new guy" on the block had something new to show one

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

 

[Guest]

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

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

 

[T. Valko]

Thanks to both Rick and Ron.

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

 

[T. Valko]

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.

 

[T. Valko]

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.

 

[Harlan Grove]

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

 

[Harlan Grove]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[T. Valko]

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>;--@--")