Text in formula?

  • Thread starter Thread starter StargateFan
  • Start date Start date
S

StargateFan

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")
Click to expand...

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
 
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
 
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.
Click to expand...

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
 
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.
Click to expand...

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
 
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
 
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
Click to expand...

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
 
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.
Click to expand...

Hi, Ron! This seems to be doing the job just fine. Thanks for
this. :oD
 
Glad to help. Thanks for the feedback.
Click to expand...

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