Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"?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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Columns | Excel Discussion (Misc queries) | |||
Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions | |||
Auto Skipping and protected cells | Excel Discussion (Misc queries) | |||
Having a due date flag in a different colour 30 days from current. | Excel Worksheet Functions | |||
Date issue between Windows and Macintosh version | Excel Discussion (Misc queries) |