How to add text, i.e., " -- Week: _________" to the formula in this message?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

StargateFanFromWork - 20 Nov 2007 01:11 GMT

Because of a unique but complicated requirement, custom formatting a cell
with
yyyy" -- Week: _________"
didn't work anymore. But I fixed it by using this in the cell formula
instead:
=IF(A2<>"",TEXT($A$2,"yyyy"),"")
(I was getting #VALUE! before I did this change above due to special formula
requirements which this group helped out with over the weekend).
This formula above gives me:
"2007".

Instead of just the year, i.e., "2007", how can I change the formula from
the just "2007" to display the year _and_ some text instead:
2007 -- Week: _________
where the year is achieved through "yyyy" on A2, etc.?

I tried all sorts of wacky things along the line of
=IF(A2<>"",TEXT($A$2,"yyyy" & " -- Week: _________"),"")
but, naturally, no luck no matter what I tried :(. Thanks. Blunderwoman
still blundering around with this one <g>.

Reply to this Message

Pete_UK - 20 Nov 2007 01:38 GMT

Try this:

=IF(A2<>"",TEXT($A$2,"yyyy")&" -- Week: _________","")

Hope this helps.

Pete

> Because of a unique but complicated requirement, custom formatting a cell
> with
[quoted text clipped - 16 lines]
> but, naturally, no luck no matter what I tried :(. Thanks. Blunderwoman
> still blundering around with this one <g>.

Reply to this Message

StargateFan - 20 Nov 2007 12:32 GMT

>Try this:
>
>=IF(A2<>"",TEXT($A$2,"yyyy")&" -- Week: _________","")
>
>Hope this helps.

Beautiful! Sure does, Pete. I just created a new spreadsheet in my
TIPS folder with all the date formatting tricks for the formula and
cell format that I've ever needed. This sheet now must cover just
about every situation I've ever needed re dates dependent on a
different cell.

Thanks. :oD

>Pete
>
[quoted text clipped - 18 lines]
>> but, naturally, no luck no matter what I tried :(. Thanks. Blunderwoman
>> still blundering around with this one <g>.

Reply to this Message

Pete_UK - 20 Nov 2007 12:49 GMT

Glad to hear that, and thanks for feeding back. You could, of course,
put any text string before or after the TEXT function and join them
with &.

Pete

> On Mon, 19 Nov 2007 17:38:39 -0800 (PST), Pete_UK
>
[quoted text clipped - 37 lines]
>
> - Show quoted text -

Reply to this Message

StargateFan - 20 Nov 2007 13:03 GMT

>Glad to hear that, and thanks for feeding back. You could, of course,
>put any text string before or after the TEXT function and join them
>with &.

Thanks, yes. Once I saw what else was needed with the commas and
quotations marks. That's where I was going wrong, not knowing what
went where, I think. I had funn playing around with this format and
did up several different kinds of date and text string examples in my
examples sheet, both with and without things like addition.

Cheers. :oD

>Pete
>
[quoted text clipped - 39 lines]
>>
>> - Show quoted text -

Reply to this Message

Pete_UK - 20 Nov 2007 14:00 GMT

You're welcome.

Pete

> On Tue, 20 Nov 2007 04:49:36 -0800 (PST), Pete_UK
>
[quoted text clipped - 56 lines]
>
> - Show quoted text -

Reply to this Message