Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Squirel Eater
 
Posts: n/a
Default set a date in columns 7 days apart

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?
  #2   Report Post  
CLR
 
Posts: n/a
Default

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



"Squirel Eater" <Squirel wrote in message
...
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?



  #3   Report Post  
Squirel Eater
 
Posts: n/a
Default

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

"CLR" wrote:

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



"Squirel Eater" <Squirel wrote in message
...
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?




  #4   Report Post  
CLR
 
Posts: n/a
Default

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





"Squirel Eater" wrote in message
...
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

"CLR" wrote:

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



"Squirel Eater" <Squirel wrote in

message
...
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?






  #5   Report Post  
Squirel Eater
 
Posts: n/a
Default

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????

"CLR" wrote:

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





"Squirel Eater" wrote in message
...
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

"CLR" wrote:

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



"Squirel Eater" <Squirel wrote in

message
...
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?








  #6   Report Post  
Squirel Eater
 
Posts: n/a
Default

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.

"CLR" wrote:

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





"Squirel Eater" wrote in message
...
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

"CLR" wrote:

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



"Squirel Eater" <Squirel wrote in

message
...
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?






  #7   Report Post  
Marc Fleury
 
Posts: n/a
Default

"?B?U3F1aXJlbCBFYXRlcg==?="
wrote

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


Actually, it's January 31st (1900)

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


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


--
Marc.
  #8   Report Post  
Bob Phillips
 
Posts: n/a
Default

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)


"Squirel Eater" wrote in message
...
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????

"CLR" wrote:

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





"Squirel Eater" wrote in

message
...
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

"CLR" wrote:

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



"Squirel Eater" <Squirel wrote in

message
...
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?








  #9   Report Post  
Damon Longworth
 
Posts: n/a
Default

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


"Squirel Eater" wrote in message
...
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.

"CLR" wrote:

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





"Squirel Eater" wrote in message
...
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

"CLR" wrote:

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



"Squirel Eater" <Squirel wrote in

message
...
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?








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Columns FemIce Excel Discussion (Misc queries) 1 September 28th 05 09:29 AM
Calculating days between current date and a date in future NETWORKDAYS() function Faheem Khan Excel Worksheet Functions 2 February 10th 05 08:18 PM
Auto Skipping and protected cells Dave Peterson Excel Discussion (Misc queries) 6 January 28th 05 12:35 AM
Having a due date flag in a different colour 30 days from current. mike099 Excel Worksheet Functions 1 January 20th 05 04:34 AM
Date issue between Windows and Macintosh version dlg1967 Excel Discussion (Misc queries) 4 January 19th 05 04:51 PM


All times are GMT +1. The time now is 11:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"