Date format in a text string  
Author Message
Gaby





PostPosted: Mon Jul 02 23:49:01 CDT 2007 Top

worksheet functions >> 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.

Excel513  
 
 
T





PostPosted: Mon Jul 02 23:49:01 CDT 2007 Top

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




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


 
 
Rick





PostPosted: Tue Jul 03 01:34:33 CDT 2007 Top

worksheet functions >> 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
 
 
T





PostPosted: Tue Jul 03 02:05:47 CDT 2007 Top

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

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


 
 
Rick





PostPosted: Tue Jul 03 09:15:39 CDT 2007 Top

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

 
 
RonCoderre





PostPosted: Tue Jul 03 09:56:01 CDT 2007 Top

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




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





PostPosted: Tue Jul 03 10:23:53 CDT 2007 Top

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

 
 
RonCoderre





PostPosted: Tue Jul 03 10:36:05 CDT 2007 Top

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




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





PostPosted: Tue Jul 03 11:01:07 CDT 2007 Top

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

 
 
T





PostPosted: Tue Jul 03 13:00:35 CDT 2007 Top

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




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





PostPosted: Tue Jul 03 13:57:23 CDT 2007 Top

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




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


 
 
T





PostPosted: Tue Jul 03 14:06:01 CDT 2007 Top

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




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


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


 
 
Harlan





PostPosted: Tue Jul 03 14:10:18 CDT 2007 Top

worksheet functions >> Date format in a text string
...
>That's because the TEXT function will only apply number formats.

A1 contains the text abc. B1 contains the formula



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

 
 
Harlan





PostPosted: Tue Jul 03 14:14:14 CDT 2007 Top

worksheet functions >> Date format in a text string
...
>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





PostPosted: Tue Jul 03 14:24:55 CDT 2007 Top

worksheet functions >> Date format in a text string > A1 contains the text abc. B1 contains the formula
>

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



Rick

 
 
T





PostPosted: Tue Jul 03 15:06:36 CDT 2007 Top

worksheet functions >> Date format in a text string


> ...
>>That's because the TEXT function will only apply number formats.
>
> A1 contains the text abc. B1 contains the formula
>

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



--
Biff
Microsoft Excel MVP