Text in formula?

What a pain in the neck to come back home after being away and not
being able to access the newsgroups via my newsreader (http://
www.gmayor.com/MSNews.htm).  Anyway ... <g>

I have this formula in cell A16:

="Don't take today:  " &+A15+1

I got it from googling for text and formulas in same cell.  I've tried
formatting cell as general and text but nothing comes out right.

The result in A16 should say:

Don't take today:  Tue.Sep.14.2010 but instead it says:

Don't take today:  40435

Can anyone advise how to fix this?

Thanks!  :oD


0
8/30/2010 3:07:35 PM
excel 39879 articles. 2 followers. Follow

12 Replies
636 Views

Similar Articles

[PageSpeed] 52

On Mon, 30 Aug 2010 08:07:35 -0700 (PDT), StargateFan
<StargateFan@mailinator.com> wrote:

>What a pain in the neck to come back home after being away and not
>being able to access the newsgroups via my newsreader (http://
>www.gmayor.com/MSNews.htm).  Anyway ... <g>
>
>I have this formula in cell A16:
>
>="Don't take today:  " &+A15+1
>
>I got it from googling for text and formulas in same cell.  I've tried
>formatting cell as general and text but nothing comes out right.
>
>The result in A16 should say:
>
>Don't take today:  Tue.Sep.14.2010 but instead it says:
>
>Don't take today:  40435
>
>Can anyone advise how to fix this?
>
>Thanks!  :oD
>

Perhaps (without knowing the contents of A15 or what you mean by
&+A15+1 :


="Don't take today: " & text(A15+1,"ddd.mmm.dd.yyyy")

0
ron6368 (329)
8/30/2010 4:08:54 PM
On Aug 30, 11:08 am, Ron Rosenfeld <r...@nospam.net> wrote:
> On Mon, 30 Aug 2010 08:07:35 -0700 (PDT), StargateFan
>
>
>
> <Stargate...@mailinator.com> wrote:
> >What a pain in the neck to come back home after being away and not
> >being able to access the newsgroups via my newsreader (http://
> >www.gmayor.com/MSNews.htm).  Anyway ... <g>
>
> >I have this formula in cell A16:
>
> >="Don't take today:  " &+A15+1
>
> >I got it from googling for text and formulas in same cell.  I've tried
> >formatting cell as general and text but nothing comes out right.
>
> >The result in A16 should say:
>
> >Don't take today:  Tue.Sep.14.2010 but instead it says:
>
> >Don't take today:  40435
>
> >Can anyone advise how to fix this?
>
> >Thanks!  :oD
>
> Perhaps (without knowing the contents of A15 or what you mean by
> &+A15+1 :
>
> ="Don't take today: " & text(A15+1,"ddd.mmm.dd.yyyy")

A15 has a date in it.  It's a regular date with no text needed.

A16 took this and displayed the date correctly but all the cells below
did not and only display #Value.  All these cells are "General" format
so don't know why it's doing the  first one okay but not the rest.
What could be causing this?

Thanks.  :oD


0
8/30/2010 6:38:38 PM
The reason for the TEXT function is to prevent the 40435 which is the serial
number for Sept 14, 2010

If A16 onward are formatted General and are real dates they should be displaying
a 5 digit number.

My guess is those "dates" are not real dates.

Hence the error.

Try dragging A15 down.


Gord Dibben     MS Excel MVP

On Mon, 30 Aug 2010 11:38:38 -0700 (PDT), StargateFan
<StargateFan@mailinator.com> wrote:

>On Aug 30, 11:08 am, Ron Rosenfeld <r...@nospam.net> wrote:
>> On Mon, 30 Aug 2010 08:07:35 -0700 (PDT), StargateFan
>>
>>
>>
>> <Stargate...@mailinator.com> wrote:
>> >What a pain in the neck to come back home after being away and not
>> >being able to access the newsgroups via my newsreader (http://
>> >www.gmayor.com/MSNews.htm).  Anyway ... <g>
>>
>> >I have this formula in cell A16:
>>
>> >="Don't take today:  " &+A15+1
>>
>> >I got it from googling for text and formulas in same cell.  I've tried
>> >formatting cell as general and text but nothing comes out right.
>>
>> >The result in A16 should say:
>>
>> >Don't take today:  Tue.Sep.14.2010 but instead it says:
>>
>> >Don't take today:  40435
>>
>> >Can anyone advise how to fix this?
>>
>> >Thanks!  :oD
>>
>> Perhaps (without knowing the contents of A15 or what you mean by
>> &+A15+1 :
>>
>> ="Don't take today: " & text(A15+1,"ddd.mmm.dd.yyyy")
>
>A15 has a date in it.  It's a regular date with no text needed.
>
>A16 took this and displayed the date correctly but all the cells below
>did not and only display #Value.  All these cells are "General" format
>so don't know why it's doing the  first one okay but not the rest.
>What could be causing this?
>
>Thanks.  :oD
>
0
phnorton (279)
8/30/2010 7:20:07 PM
On Mon, 30 Aug 2010 11:38:38 -0700 (PDT), StargateFan
<StargateFan@mailinator.com> wrote:

>A15 has a date in it.  It's a regular date with no text needed.
>
>A16 took this and displayed the date correctly but all the cells below
>did not and only display #Value.  All these cells are "General" format
>so don't know why it's doing the  first one okay but not the rest.
>What could be causing this?
>
>Thanks.  :oD


A #VALUE! error is the result of a value being used in the formula
being of the wrong data type.

Without knowing the formula, it's hard to come up with a solution.

0
ron6368 (329)
8/30/2010 8:05:44 PM
On Aug 30, 3:05 pm, Ron Rosenfeld <r...@nospam.net> wrote:
> On Mon, 30 Aug 2010 11:38:38 -0700 (PDT), StargateFan
>
> <Stargate...@mailinator.com> wrote:
> >A15 has a date in it.  It's a regular date with no text needed.
>
> >A16 took this and displayed the date correctly but all the cells below
> >did not and only display #Value.  All these cells are "General" format
> >so don't know why it's doing the  first one okay but not the rest.
> >What could be causing this?
>
> >Thanks.  :oD
>
> A #VALUE! error is the result of a value being used in the formula
> being of the wrong data type.
>
> Without knowing the formula, it's hard to come up with a solution.

Oh, well, the formula is the one above:

="Don't take today: " & text(A15+1,"ddd.mmm.dd.yyyy")

All the ones above do are to display the date + 1:  i.e., A2 is the
starting date.  The cells below build upon that one by one day.  I
have custom date format in those of:  ddd.mmm.dd.yyyy

But after 2 weeks of dates, at A17, the user stops the activity hence
the text added into the field information above to signal that (plus
cell colouring is dark grey vs different colour above).

All these fields with text are formatted as General and A17 does
display the information correctly with text and correct date, but all
the pertinent cells below below display correctly even though they're
also General format.  They show up as #VALUE! and I don't know how to
fix this.  Why A17 is okay and the rest not is a mystery.

:oD

0
8/31/2010 9:17:55 AM
> All these fields with text are formatted as General and A17 does
> display the information correctly with text and correct date, but all
> the pertinent cells below below display correctly even though they're
> also General format.  They show up as #VALUE! and I don't know how to
> fix this.  Why A17 is okay and the rest not is a mystery.

Oops, sorry, typo (too early in the morning! <g>).
The line should read:

"> All these fields with text are formatted as General and A17 does
> display the information correctly with text and correct date, but all
> the pertinent cells below display _INCORRECTLY_ even though they're
> also General format.  They show up as #VALUE! and I don't know how to"
0
8/31/2010 9:20:03 AM
I'm surprised that you put the formula in A16 to refer to a date in
A15 - you would normally put the formula in another column on the same
row and then copy it down. Could you give an example of your data
layout?

Pete

On Aug 31, 10:20=A0am, StargateFan <Stargate...@mailinator.com> wrote:
> > All these fields with text are formatted as General and A17 does
> > display the information correctly with text and correct date, but all
> > the pertinent cells below below display correctly even though they're
> > also General format. =A0They show up as #VALUE! and I don't know how to
> > fix this. =A0Why A17 is okay and the rest not is a mystery.
>
> Oops, sorry, typo (too early in the morning! <g>).
> The line should read:
>
> "> All these fields with text are formatted as General and A17 does
>
>
>
> > display the information correctly with text and correct date, but all
> > the pertinent cells below display _INCORRECTLY_ even though they're
> > also General format. =A0They show up as #VALUE! and I don't know how to=
"- Hide quoted text -
>
> - Show quoted text -

0
pashurst (2576)
8/31/2010 9:31:22 AM
On Aug 31, 4:31 am, Pete_UK <pashu...@auditel.net> wrote:
> I'm surprised that you put the formula in A16 to refer to a date in
> A15 - you would normally put the formula in another column on the same
> row and then copy it down. Could you give an example of your data
> layout?
>
> Pete

Thanks, Pete.

Well, I've always worked this way so if I was doing something wrong,
it's only turning up now because I just would like to add some text to
the formula.  I tried doing it via custom formatting of the cell but
that didn't work, so this seemed easier.

Here's how the data looks right now:

A1:   Start date:  (text)
A2:   Mon.Aug.30.2010  (start date entered by user)

A3:   Tue.Aug.31.2010
A4:   Wed.Sep.01.2010
A5:   Thu.Sep.02.2010
A6:   Fri.Sep.03.2010
A7:   Sat.Sep.04.2010
A8:   Sun.Sep.05.2010
A9:   Mon.Sep.06.2010
A10:  Tue.Sep.07.2010
A11:  Wed.Sep.08.2010
A12:  Thu.Sep.09.2010
A13:  Fri.Sep.10.2010
A14:  Sat.Sep.11.2010
A15:  Sun.Sep.12.2010
A16:  Mon.Sep.13.2010
A17:  Don't take today:  Tue.Sep.14.2010
A18:  #VALUE!
A19:  #VALUE!
A20:  ... (several rows below say #VALUE!)
A31:  (Regular date with no text for 2 more weeks starts here)

Thanks.  :oD

0
8/31/2010 9:41:53 AM
On Tue, 31 Aug 2010 02:17:55 -0700 (PDT), StargateFan
<StargateFan@mailinator.com> wrote:

>Oh, well, the formula is the one above:
>
>="Don't take today: " & text(A15+1,"ddd.mmm.dd.yyyy")
>
>All the ones above do are to display the date + 1:  i.e., A2 is the
>starting date.  The cells below build upon that one by one day.  I
>have custom date format in those of:  ddd.mmm.dd.yyyy
>
>But after 2 weeks of dates, at A17, the user stops the activity hence
>the text added into the field information above to signal that (plus
>cell colouring is dark grey vs different colour above).
>
>All these fields with text are formatted as General and A17 does
>display the information correctly with text and correct date, but all
>the pertinent cells below below display correctly even though they're
>also General format.  They show up as #VALUE! and I don't know how to
>fix this.  Why A17 is okay and the rest not is a mystery.
>
>:oD

The reason that some formulas show #VALUE! is because they are trying
to do arithmetic operations on cells which contain text.  Since A17
refers to A16, and A16 contains a text string, it fails when you try
to add one.

With your setup, instead of adding one to the preceding cell, I would
add an appropriate number to your inital starting date in A2.  A2
should always be a number (dates are really just numbers), so you
shouldn't roun into any problem.

One way of doing that would be:

A2:	Starting Date
A3:	=$A$2+ROWS($1:1)

Fill down to A16  The ROWS argument will automatically adjust to
return a proper count.

A17:	
="Don't take today: " & TEXT($A$2+ROWS($1:15),"ddd.mmm.dd.yyyy")

Fill down as needed.

Oh, if you insert a row or cell in this column, it will NOT adjust the
ROWS($1:n) argument, but rather they will stay as you initially set it
up.  This may or may not be desireable, but should be taken into
account.
0
ron6368 (329)
8/31/2010 11:29:08 AM
On Aug 31, 6:29 am, Ron Rosenfeld <r...@nospam.net> wrote:
> On Tue, 31 Aug 2010 02:17:55 -0700 (PDT), StargateFan
>
[snip]
>
>
> With your setup, instead of adding one to the preceding cell, I would
> add an appropriate number to your inital starting date in A2.  A2
> should always be a number (dates are really just numbers), so you
> shouldn't roun into any problem.
>
> One way of doing that would be:
>
> A2:     Starting Date
> A3:     =$A$2+ROWS($1:1)
>
> Fill down to A16  The ROWS argument will automatically adjust to
> return a proper count.
>
> A17:
> ="Don't take today: " & TEXT($A$2+ROWS($1:15),"ddd.mmm.dd.yyyy")
>
> Fill down as needed.
>
> Oh, if you insert a row or cell in this column, it will NOT adjust the
> ROWS($1:n) argument, but rather they will stay as you initially set it
> up.  This may or may not be desireable, but should be taken into
> account.

Hi, Ron!  This seems to be doing the job just fine.  Thanks for
this.  :oD
0
8/31/2010 5:36:33 PM
On Tue, 31 Aug 2010 10:36:33 -0700 (PDT), StargateFan
<StargateFan@mailinator.com> wrote:

>Hi, Ron!  This seems to be doing the job just fine.  Thanks for
>this.  :oD

Glad to help.  Thanks for the feedback.
0
ron6368 (329)
8/31/2010 6:11:17 PM
On Aug 31, 1:11 pm, Ron Rosenfeld <r...@nospam.net> wrote:
> On Tue, 31 Aug 2010 10:36:33 -0700 (PDT), StargateFan
>
> <Stargate...@mailinator.com> wrote:
> >Hi, Ron!  This seems to be doing the job just fine.  Thanks for
> >this.  :oD
>
> Glad to help.  Thanks for the feedback.

<g>

I printed up the sheet and have it on hand.  Tested it for future
dates.  Great thing and we won't get messed up with any dates now.

I don't know what we'd do without Excel.  It's the program I use the
most, besides Outlook at work, out of the whole suite.

:oD
0
9/1/2010 1:22:35 PM
Reply:

Similar Artilces: