set a date in columns 7 days apart

S

Squirel Eater

How can I set a date in subsequent columns that are 7 days apart? I have
tried
=DAY(IF(B3="","",B3+7)) but if given a date March 6, 2005 it will show 31
days in April. Any suggestions?
 
C

CLR

If you put a date in A1, then in B1 put

=A1+7, and copy it over to as many columns to the right as you
wish.......each will increment 7 days.

Vaya con Dios,
Chuck, CABGx3
 
S

Squirel Eater

Yes I agree, but, it will return a date of April 31, 2005 even though this
date is not true. I am looking for a surefire solution. By the way

Last day of the this month:
=DATE(YEAR("04/01/2005"),MONTH("04/01/2005")+1,1)-1

returns 4/30/2005

Thanks for the input
 
C

CLR

Using XL2k, when I put 3/6/2005 in A1 and =A1+7 in B1 and drag B1 out to I1,
I get:

B1= 3/13/2005
C1= 3/20/2005
D1= 3/27/2005
E1= 4/3/2005
F1= 4/10/2005
G1 = 4/17/2005
H1= 4/24/2005
I1= 5/1/2005

"No 4/31/2005 in sight"
Is this not what you asked for?
"WHERE" are you getting the 4/31/2005?, and by what means?

Vaya con Dios,
Chuck, CABGx3
 
S

Squirel Eater

Well....

enter 3/6/2005 in A1

B3 enter formula =DAY($A$1)
C3 thru P3 gets =DAY(IF(B3="","",B3+7))

you will get

6
13
20
27
3
10
17
24
31 <this should be May 7 but it is April 31
7
14

but wait maybe it is not interpreting B3 as 4/6/2005????
 
S

Squirel Eater

Thanks! I have solved the problem. Your info below gave me the clue. I
will use a row with the dates as you did. I will then turn that row into a
day() and that will do what I wanted. I assume I will be able to hide the
unwanted row.

Thanks again.
 
M

Marc Fleury

"=?Utf-8?B?U3F1aXJlbCBFYXRlcg==?="
enter 3/6/2005 in A1

B3 enter formula =DAY($A$1)
C3 thru P3 gets =DAY(IF(B3="","",B3+7))

you will get

6
13
20
27
3
10
17
24
31 <this should be May 7 but it is April 31
Click to expand...

Actually, it's January 31st (1900)
but wait maybe it is not interpreting B3 as 4/6/2005????
Click to expand...

Right.

Try two columns...

3/6/2005 =DAY(A1)
=A1+7 =DAY(A2)
=A2+7 =DAY(A3)
=A3+7 =DAY(A4)
etc


column B will correctly show
6
13
20
27
3
10
17
24
1
 
B

Bob Phillips

You are not working with dates anymore/

Once you do =DAY(A1) it is just a day number not a date. Adding 7 to it just
gets another number, and by doing =DAY(IF(B3="","",B3+7)), because of the
way Excel stores dates, it gets the day number of some date in 1900, 6th day
(Jan 6th), 13th, 20th, 27th, 34th (3rd Feb). etc. So you are not dealing
with the months or years you think you are.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Damon Longworth

Why do you need two columns? You can format the cell to only display the day
and leave the dates +7 in the cell.



--
Damon Longworth

Don't miss out on the 2005 Excel User Conference
Sept 16th and 17th
Stockyards Hotel - Ft. Worth, Texas
www.ExcelUserConference.com
 
Top