Date format in a text string |
|
Author |
Message |
Gaby
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
|
|
|
|
|
|