Date Formulas

[slider]

In previous versions of Excel, I could input a date in a cell and then write a formula of the form "=A1+7" in the adjacent cell to produce a date 7 days from the initial one. I could then fill to produce a row of dates as column headings for data to be entered beneath. I just tried doing that same thing in XL 2008 a got #VALUE! error. What has changed?

 

[CyberTaz]

Works fine here - although I'm not sure I would prefer to do it that way
unless the base date needs to be changed periodically.

What more can you say about the situation? Exactly what steps are you taking
to do the fill? Have you double-checked your entries for formatting?

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[slider]

Triple checked formatting and tried it in a new worksheet since the original attempt was in a 2004 sheet. If I input a date in A1 and then write a formula in B1 of the form "=A1+7," I get a #VALUE! error in XL 2008 even before the fill. Just that simple reference to A1+7 is giving me the error. Like I said, I fixed it with the DATE function, but it is a PITA.

 

[JE McGimpsey]

In previous versions of Excel, I could input a date in a cell and then write
a formula of the form "=A1+7" in the adjacent cell to produce a date 7 days
from the initial one. I could then fill to produce a row of dates as column
headings for data to be entered beneath. I just tried doing that same thing
in XL 2008 a got #VALUE! error. What has changed?

A #VALUE! error indicates that the entry in A1 was entered as Text. Make
sure the Number Format of A1 is set to a Date, and re-enter the date.

 

[slider]

Strangely enough, after a relaunch of the application, it works fine now either way. Honestly, it did not work. I did everything the same. Gremlins?

 

[Phillip Jones]

try =Sum (A1+1) for across.

If doing a calendar
try =Sum(A1+7) to make Seven days later.

I have to create a small desktop calendar for my mother about the size
of those fold out Calendars from a Funeral home. So she can keep up with
appointments.

so I have to do this yearly

formula would be:

1 =Sum(A1+1) =Sum(A1+1) =Sum(A1+1) =Sum(A1+1) =Sum(A1+1) =Sum(A1+1)
=Sum(A1+7) =Sum(A1+7) =Sum(A1+7) =Sum(A1+7) =Sum(A1+7) =Sum(A1+7)
=Sum(A1+7)

the next row would be b1
the next C1 and so on.

I still have to tweak days at the beginning and the end of the months
but after getting the basic formula down its just a matter of moving
items around from year to year. I can knock one out in about 20 minutes
or so.

Triple checked formatting and tried it in a new worksheet since the
original attempt was in a 2004 sheet. If I input a date in A1 and then
write a formula in B1 of the form "=A1+7," I get a #VALUE! error in XL
2008 even before the fill. Just that simple reference to A1+7 is giving
me the error. Like I said, I fixed it with the DATE function, but it is
a PITA.

--
------------------------------------------------------------------------
Phillip M. Jones, CET |LIFE MEMBER: VPEA ETA-I, NESDA, ISCET, Sterling
616 Liberty Street |Who's Who. PHONE:276-632-5045, FAX:276-632-0868
Martinsville Va 24112 |[email protected], ICQ11269732, AIM pjonescet
------------------------------------------------------------------------

If it's "fixed", don't "break it"!

mailto[email protected]

<http://www.kimbanet.com/~pjones/default.htm>
<http://www.kimbanet.com/~pjones/90th_Birthday/index.htm>
<http://www.kimbanet.com/~pjones/Fulcher/default.html>
<http://www.kimbanet.com/~pjones/Harris/default.htm>
<http://www.kimbanet.com/~pjones/Jones/default.htm>

<http://www.vpea.org>

 

[JE McGimpsey]

Note that

=SUM(A1+7)

gives exactly the same result as

=A1+7

since all it does is wrap the SUM() function around the single value
result of the addition. It's therefore less efficient.

On the other hand,

=SUM(A1, 7)

evaluates both arguments (A1 & 7) and sums the numeric ones.

SUM() ignores text, so if there's text in A1

=SUM(A1,7)

will return 7.

 

[Phillip Jones]

Did nit explain it well did I?

if you go across use =sum(A1=1)

if you go down then use =Sum(A1+7

by using the formulas above

you would have

1 2 3 4 5 6 7 (use the =sum(A1=1) for first row

8 9 10 11 12 13 14 using Drag down the cell (a! and create the formula
from it =sum(A1+7) The drag this cell over which create =sum(A2+7), =
sum(2+7 and so on.

I can send the calendar to any one and they can look at the formulas.

(Another reason I wouldn't make a good MVP)

Note that

=SUM(A1+7)

gives exactly the same result as

=A1+7

since all it does is wrap the SUM() function around the single value
result of the addition. It's therefore less efficient.

On the other hand,

=SUM(A1, 7)

evaluates both arguments (A1 & 7) and sums the numeric ones.

SUM() ignores text, so if there's text in A1

=SUM(A1,7)

will return 7.

--
------------------------------------------------------------------------
Phillip M. Jones, CET |LIFE MEMBER: VPEA ETA-I, NESDA, ISCET, Sterling
616 Liberty Street |Who's Who. PHONE:276-632-5045, FAX:276-632-0868
Martinsville Va 24112 |[email protected], ICQ11269732, AIM pjonescet
------------------------------------------------------------------------

If it's "fixed", don't "break it"!

mailto[email protected]

<http://www.kimbanet.com/~pjones/default.htm>
<http://www.kimbanet.com/~pjones/90th_Birthday/index.htm>
<http://www.kimbanet.com/~pjones/Fulcher/default.html>
<http://www.kimbanet.com/~pjones/Harris/default.htm>
<http://www.kimbanet.com/~pjones/Jones/default.htm>

<http://www.vpea.org>

 

[Bob Greenblatt]

Did nit explain it well did I? No, and still didn't!

if you go across use =sum(A1=1)

I assume you really mean =sum(A1+1). But as JE pointed out, that's not
necessary. Just use =A1+1 in B1 and fill it to the right.

if you go down then use =Sum(A1+7

Now, in A2, use =A1+7, and fill this to the right. The select the cells in
the row, and fill these down. And you will get the desired result.

by using the formulas above

you would have

1 2 3 4 5 6 7 (use the =sum(A1=1) for first row

8 9 10 11 12 13 14 using Drag down the cell (a! and create the formula
from it =sum(A1+7) The drag this cell over which create =sum(A2+7), =
sum(2+7 and so on.

I can send the calendar to any one and they can look at the formulas.

If my explanation isn't clear enough. You can send it to me.

 

[Phillip Jones]

I still didn't get it right. :-(

But I think you explained my mis-typings.

In any event in Excel2004 I've found it doesn't work right unless you
=sum(whatever+whatever) just using the = sign with out sum doesn't seem
to work.

I assume you really mean =sum(A1+1). But as JE pointed out, that's not
necessary. Just use =A1+1 in B1 and fill it to the right.
Now, in A2, use =A1+7, and fill this to the right. The select the cells in
the row, and fill these down. And you will get the desired result.
If my explanation isn't clear enough. You can send it to me.

--
------------------------------------------------------------------------
Phillip M. Jones, CET |LIFE MEMBER: VPEA ETA-I, NESDA, ISCET, Sterling
616 Liberty Street |Who's Who. PHONE:276-632-5045, FAX:276-632-0868
Martinsville Va 24112 |[email protected], ICQ11269732, AIM pjonescet
------------------------------------------------------------------------

If it's "fixed", don't "break it"!

mailto[email protected]

<http://www.kimbanet.com/~pjones/default.htm>
<http://www.kimbanet.com/~pjones/90th_Birthday/index.htm>
<http://www.kimbanet.com/~pjones/Fulcher/default.html>
<http://www.kimbanet.com/~pjones/Harris/default.htm>
<http://www.kimbanet.com/~pjones/Jones/default.htm>

<http://www.vpea.org>

 

[Bob Greenblatt]

I still didn't get it right. :-(

But I think you explained my mis-typings.

In any event in Excel2004 I've found it doesn't work right unless you
=sum(whatever+whatever) just using the = sign with out sum doesn't seem
to work.

Then something is screwed up in your sheet, or you are still doing something
wrong. It is not necessary to use SUM in this case, and it only serves to
slow down the calculations. Again, send me the sheet if you want it
corrected, or a further explanation of what is going wrong.

 

[Phillip Jones]

I sent my Calendar to you.

while its true if you are just summing a series of numbers

=1+2-4 Then you don't need Sum.

But if your doing something to cells, say cell A1 plus 1 or even A1 plus
A2; then Excel 2004 will throw a warning that the formula is incorrect
and ask whether you want to correct it. using =Sum(A1+1) or =Sum(A1+A2)
doesn't bring up the warning.

What I would really love to do is when I need to create this calendar
each year I didn't have to change the first and last week's numbers. to
account for the day shift from year to year. but since its only accounts
for a few days, I can live with what I have.

Since there is no personal info if it happens to be good enough it can
be submitted as a template for people that need such.

Then something is screwed up in your sheet, or you are still doing something
wrong. It is not necessary to use SUM in this case, and it only serves to
slow down the calculations. Again, send me the sheet if you want it
corrected, or a further explanation of what is going wrong.

--
------------------------------------------------------------------------
Phillip M. Jones, CET |LIFE MEMBER: VPEA ETA-I, NESDA, ISCET, Sterling
616 Liberty Street |Who's Who. PHONE:276-632-5045, FAX:276-632-0868
Martinsville Va 24112 |[email protected], ICQ11269732, AIM pjonescet
------------------------------------------------------------------------

If it's "fixed", don't "break it"!

mailto[email protected]

<http://www.kimbanet.com/~pjones/default.htm>
<http://www.kimbanet.com/~pjones/90th_Birthday/index.htm>
<http://www.kimbanet.com/~pjones/Fulcher/default.html>
<http://www.kimbanet.com/~pjones/Harris/default.htm>
<http://www.kimbanet.com/~pjones/Jones/default.htm>

<http://www.vpea.org>

 

[JE McGimpsey]

But if your doing something to cells, say cell A1 plus 1 or even A1 plus
A2; then Excel 2004 will throw a warning that the formula is incorrect
and ask whether you want to correct it. using =Sum(A1+1) or =Sum(A1+A2)
doesn't bring up the warning.

XL04 certainly *shouldn't* bring up a warning that the formula's
incorrect...

....or if it does, it should bring up the *same* warning for SUM().

What warning is displayed?

 

[Phillip Jones]

Something about formula formatted incorrectly do you want to correct
incorrect formula do you wish to correct) I ran into it so long ago that
I just started using =Sum() instead to not get the warning. evidently
the formatting for dealing with cells has to be different than strictly
with numbers. I don't know just know what it does for me.

XL04 certainly *shouldn't* bring up a warning that the formula's
incorrect...

...or if it does, it should bring up the *same* warning for SUM().

What warning is displayed?

--
------------------------------------------------------------------------
Phillip M. Jones, CET |LIFE MEMBER: VPEA ETA-I, NESDA, ISCET, Sterling
616 Liberty Street |Who's Who. PHONE:276-632-5045, FAX:276-632-0868
Martinsville Va 24112 |[email protected], ICQ11269732, AIM pjonescet
------------------------------------------------------------------------

If it's "fixed", don't "break it"!

mailto[email protected]

<http://www.kimbanet.com/~pjones/default.htm>
<http://www.kimbanet.com/~pjones/90th_Birthday/index.htm>
<http://www.kimbanet.com/~pjones/Fulcher/default.html>
<http://www.kimbanet.com/~pjones/Harris/default.htm>
<http://www.kimbanet.com/~pjones/Jones/default.htm>

<http://www.vpea.org>