|
Date issue to automate calendar
I have a start date of 11/1/2008 - What I want to populate is a table that
has columns for Jan-08, Feb-08, Mar-08 etc begining with January ending with December. I know I can type the column headers, but I want the column headers to display the correct year if my start date becomes 2/1/2009. Therefore Jan-09, Feb-09, Mar-09 etc. Is there any formula that I can use? -- Babs |
Date issue to automate calendar
You can use the following formula.
=DATE(YEAR($A1),MONTH($A1)+COLUMN()-COLUMN($F1),1) This assumes that your start date is in cell A1 and this formula is place in F1 and filled to the right out to column P, which will list December. Change the reference to $F1 to the first cell in which the formula is entered. Use the $ character as shown. "Babs in Ohio" wrote in message ... I have a start date of 11/1/2008 - What I want to populate is a table that has columns for Jan-08, Feb-08, Mar-08 etc begining with January ending with December. I know I can type the column headers, but I want the column headers to display the correct year if my start date becomes 2/1/2009. Therefore Jan-09, Feb-09, Mar-09 etc. Is there any formula that I can use? -- Babs |
Date issue to automate calendar
Chip,
Thank you for your reply. I put 11/1/2008 in cell A1 and put your formula in F1. What I got is: cell F1 = 11/1/2008 cell F2 = 12/1/2008 cell F3 = 1/1/2009 Am I missing something. :-) Did I change your formula? -- Babs "Chip Pearson" wrote: You can use the following formula. =DATE(YEAR($A1),MONTH($A1)+COLUMN()-COLUMN($F1),1) This assumes that your start date is in cell A1 and this formula is place in F1 and filled to the right out to column P, which will list December. Change the reference to $F1 to the first cell in which the formula is entered. Use the $ character as shown. "Babs in Ohio" wrote in message ... I have a start date of 11/1/2008 - What I want to populate is a table that has columns for Jan-08, Feb-08, Mar-08 etc begining with January ending with December. I know I can type the column headers, but I want the column headers to display the correct year if my start date becomes 2/1/2009. Therefore Jan-09, Feb-09, Mar-09 etc. Is there any formula that I can use? -- Babs |
Date issue to automate calendar
Put the following in F1 and drag the formula thru Q1
=DATE(YEAR($A1),MONTH(1)+COLUMN()-COLUMN($F1),1) Then format cells F1 thru Q1 with a custom format of mmm-yy. Then enter your date in A1. If you start the formula in some other column than F, say column C, change the $F in the formula to $C. "Babs in Ohio" wrote in message ... I have a start date of 11/1/2008 - What I want to populate is a table that has columns for Jan-08, Feb-08, Mar-08 etc begining with January ending with December. I know I can type the column headers, but I want the column headers to display the correct year if my start date becomes 2/1/2009. Therefore Jan-09, Feb-09, Mar-09 etc. Is there any formula that I can use? -- Babs |
Date issue to automate calendar - elegance
Essentially the same formula but more elegant,
=DATE(YEAR($A1),1+COLUMN()-COLUMN($F1),1) "Dave Thomas" wrote in message . net... Put the following in F1 and drag the formula thru Q1 =DATE(YEAR($A1),MONTH(1)+COLUMN()-COLUMN($F1),1) Then format cells F1 thru Q1 with a custom format of mmm-yy. Then enter your date in A1. If you start the formula in some other column than F, say column C, change the $F in the formula to $C. "Babs in Ohio" wrote in message ... I have a start date of 11/1/2008 - What I want to populate is a table that has columns for Jan-08, Feb-08, Mar-08 etc begining with January ending with December. I know I can type the column headers, but I want the column headers to display the correct year if my start date becomes 2/1/2009. Therefore Jan-09, Feb-09, Mar-09 etc. Is there any formula that I can use? -- Babs |
Date issue to automate calendar
I assume you meant F1, G1 and so on?
What did you expect to get? If you meant that you didn't get Jan-08, Feb-08 that is because you cannot get a date in Excel with just month and year, you need the day as well. However if you just format F1 as mmm-yy then copy the formula to the right you will get what you want Note that 11/01/08 in the US is November the first, not that it matter since all the formula does is to take the date in A1 and change it to the first of that particular month and year -- Regards, Peo Sjoblom "Babs in Ohio" wrote in message ... Chip, Thank you for your reply. I put 11/1/2008 in cell A1 and put your formula in F1. What I got is: cell F1 = 11/1/2008 cell F2 = 12/1/2008 cell F3 = 1/1/2009 Am I missing something. :-) Did I change your formula? -- Babs "Chip Pearson" wrote: You can use the following formula. =DATE(YEAR($A1),MONTH($A1)+COLUMN()-COLUMN($F1),1) This assumes that your start date is in cell A1 and this formula is place in F1 and filled to the right out to column P, which will list December. Change the reference to $F1 to the first cell in which the formula is entered. Use the $ character as shown. "Babs in Ohio" wrote in message ... I have a start date of 11/1/2008 - What I want to populate is a table that has columns for Jan-08, Feb-08, Mar-08 etc begining with January ending with December. I know I can type the column headers, but I want the column headers to display the correct year if my start date becomes 2/1/2009. Therefore Jan-09, Feb-09, Mar-09 etc. Is there any formula that I can use? -- Babs |
Date issue to automate calendar - elegance
Or even
=DATE(YEAR($A1),COLUMNS($A$1:A1),1) -- Regards, Peo Sjoblom "Dave Thomas" wrote in message et... Essentially the same formula but more elegant, =DATE(YEAR($A1),1+COLUMN()-COLUMN($F1),1) "Dave Thomas" wrote in message . net... Put the following in F1 and drag the formula thru Q1 =DATE(YEAR($A1),MONTH(1)+COLUMN()-COLUMN($F1),1) Then format cells F1 thru Q1 with a custom format of mmm-yy. Then enter your date in A1. If you start the formula in some other column than F, say column C, change the $F in the formula to $C. "Babs in Ohio" wrote in message ... I have a start date of 11/1/2008 - What I want to populate is a table that has columns for Jan-08, Feb-08, Mar-08 etc begining with January ending with December. I know I can type the column headers, but I want the column headers to display the correct year if my start date becomes 2/1/2009. Therefore Jan-09, Feb-09, Mar-09 etc. Is there any formula that I can use? -- Babs |
Date issue to automate calendar - elegance
I was merely removing the MONTH function, yet keep it simple for people to
understand. We can obfuscate these things all we want and make them so obtuse, the beginner doesn't have a clue. "Peo Sjoblom" wrote in message ... Or even =DATE(YEAR($A1),COLUMNS($A$1:A1),1) -- Regards, Peo Sjoblom "Dave Thomas" wrote in message et... Essentially the same formula but more elegant, =DATE(YEAR($A1),1+COLUMN()-COLUMN($F1),1) "Dave Thomas" wrote in message . net... Put the following in F1 and drag the formula thru Q1 =DATE(YEAR($A1),MONTH(1)+COLUMN()-COLUMN($F1),1) Then format cells F1 thru Q1 with a custom format of mmm-yy. Then enter your date in A1. If you start the formula in some other column than F, say column C, change the $F in the formula to $C. "Babs in Ohio" wrote in message ... I have a start date of 11/1/2008 - What I want to populate is a table that has columns for Jan-08, Feb-08, Mar-08 etc begining with January ending with December. I know I can type the column headers, but I want the column headers to display the correct year if my start date becomes 2/1/2009. Therefore Jan-09, Feb-09, Mar-09 etc. Is there any formula that I can use? -- Babs |
Date issue to automate calendar - elegance
Are you saying that it easier to understand
1+COLUMN()-COLUMN($F1) vis-à-vis COLUMNS($A$1:A1) me thinks not, I believe both are confusing with regards to a date increment OTOH my formula can be put in any cell whereas yours will return the incorrect result if put anywhere but the F column -- Regards, Peo Sjoblom "Dave Thomas" wrote in message . net... I was merely removing the MONTH function, yet keep it simple for people to understand. We can obfuscate these things all we want and make them so obtuse, the beginner doesn't have a clue. "Peo Sjoblom" wrote in message ... Or even =DATE(YEAR($A1),COLUMNS($A$1:A1),1) -- Regards, Peo Sjoblom "Dave Thomas" wrote in message et... Essentially the same formula but more elegant, =DATE(YEAR($A1),1+COLUMN()-COLUMN($F1),1) "Dave Thomas" wrote in message . net... Put the following in F1 and drag the formula thru Q1 =DATE(YEAR($A1),MONTH(1)+COLUMN()-COLUMN($F1),1) Then format cells F1 thru Q1 with a custom format of mmm-yy. Then enter your date in A1. If you start the formula in some other column than F, say column C, change the $F in the formula to $C. "Babs in Ohio" wrote in message ... I have a start date of 11/1/2008 - What I want to populate is a table that has columns for Jan-08, Feb-08, Mar-08 etc begining with January ending with December. I know I can type the column headers, but I want the column headers to display the correct year if my start date becomes 2/1/2009. Therefore Jan-09, Feb-09, Mar-09 etc. Is there any formula that I can use? -- Babs |
Date issue to automate calendar
If you always want the first month of the particular year in A1 use
=DATE(YEAR($A1),COLUMNS($A$1:A1),1) format as mmm-yy and copy across -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... I assume you meant F1, G1 and so on? What did you expect to get? If you meant that you didn't get Jan-08, Feb-08 that is because you cannot get a date in Excel with just month and year, you need the day as well. However if you just format F1 as mmm-yy then copy the formula to the right you will get what you want Note that 11/01/08 in the US is November the first, not that it matter since all the formula does is to take the date in A1 and change it to the first of that particular month and year -- Regards, Peo Sjoblom "Babs in Ohio" wrote in message ... Chip, Thank you for your reply. I put 11/1/2008 in cell A1 and put your formula in F1. What I got is: cell F1 = 11/1/2008 cell F2 = 12/1/2008 cell F3 = 1/1/2009 Am I missing something. :-) Did I change your formula? -- Babs "Chip Pearson" wrote: You can use the following formula. =DATE(YEAR($A1),MONTH($A1)+COLUMN()-COLUMN($F1),1) This assumes that your start date is in cell A1 and this formula is place in F1 and filled to the right out to column P, which will list December. Change the reference to $F1 to the first cell in which the formula is entered. Use the $ character as shown. "Babs in Ohio" wrote in message ... I have a start date of 11/1/2008 - What I want to populate is a table that has columns for Jan-08, Feb-08, Mar-08 etc begining with January ending with December. I know I can type the column headers, but I want the column headers to display the correct year if my start date becomes 2/1/2009. Therefore Jan-09, Feb-09, Mar-09 etc. Is there any formula that I can use? -- Babs |
Date issue to automate calendar - elegance
I was merely correcting the formula that Chip submitted. Babs wanted to know
why it didn't work. I was just showing her with the original formula. Changing the formula completely does not demonstrate why the original does not work. Your formula is easy for me to understand but it is not obvious to beginners. "Peo Sjoblom" wrote in message ... Are you saying that it easier to understand 1+COLUMN()-COLUMN($F1) vis-à-vis COLUMNS($A$1:A1) me thinks not, I believe both are confusing with regards to a date increment OTOH my formula can be put in any cell whereas yours will return the incorrect result if put anywhere but the F column -- Regards, Peo Sjoblom "Dave Thomas" wrote in message . net... I was merely removing the MONTH function, yet keep it simple for people to understand. We can obfuscate these things all we want and make them so obtuse, the beginner doesn't have a clue. "Peo Sjoblom" wrote in message ... Or even =DATE(YEAR($A1),COLUMNS($A$1:A1),1) -- Regards, Peo Sjoblom "Dave Thomas" wrote in message et... Essentially the same formula but more elegant, =DATE(YEAR($A1),1+COLUMN()-COLUMN($F1),1) "Dave Thomas" wrote in message . net... Put the following in F1 and drag the formula thru Q1 =DATE(YEAR($A1),MONTH(1)+COLUMN()-COLUMN($F1),1) Then format cells F1 thru Q1 with a custom format of mmm-yy. Then enter your date in A1. If you start the formula in some other column than F, say column C, change the $F in the formula to $C. "Babs in Ohio" wrote in message ... I have a start date of 11/1/2008 - What I want to populate is a table that has columns for Jan-08, Feb-08, Mar-08 etc begining with January ending with December. I know I can type the column headers, but I want the column headers to display the correct year if my start date becomes 2/1/2009. Therefore Jan-09, Feb-09, Mar-09 etc. Is there any formula that I can use? -- Babs |
Date issue to automate calendar - elegance
Or even
=DATE(YEAR($A1),COLUMNS($A$1:A1),1) This further reduction seems to work also... =DATE(YEAR($A1),COLUMN(A1),1) Rick |
Date issue to automate calendar - elegance
Nice, but that is not an answer to the question which is why does Chips
formula not work. "Rick Rothstein (MVP - VB)" wrote in message ... Or even =DATE(YEAR($A1),COLUMNS($A$1:A1),1) This further reduction seems to work also... =DATE(YEAR($A1),COLUMN(A1),1) Rick |
Date issue to automate calendar - elegance
Maybe I am missing something, but Chip's formula appears to work fine for me
as long as it is placed in column F (and once you format the cells to show mmm-yy, of course). Rick "Dave Thomas" wrote in message t... Nice, but that is not an answer to the question which is why does Chips formula not work. "Rick Rothstein (MVP - VB)" wrote in message ... Or even =DATE(YEAR($A1),COLUMNS($A$1:A1),1) This further reduction seems to work also... =DATE(YEAR($A1),COLUMN(A1),1) Rick |
Date issue to automate calendar - elegance
Won't work if you insert a column, that is the whole point of using columns
vs column -- Regards, Peo Sjoblom "Rick Rothstein (MVP - VB)" wrote in message ... Or even =DATE(YEAR($A1),COLUMNS($A$1:A1),1) This further reduction seems to work also... =DATE(YEAR($A1),COLUMN(A1),1) Rick |
Date issue to automate calendar - elegance
Nice, but that is not an answer to the question which is why does Chips
formula not work. My formula did work as I intended, but Babs entered in down rows in a single column, not across columns in a single row. But as others have shown, mine was not the simplest answer.I think Rick wins the prize for simplicity. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Dave Thomas" wrote in message t... Nice, but that is not an answer to the question which is why does Chips formula not work. "Rick Rothstein (MVP - VB)" wrote in message ... Or even =DATE(YEAR($A1),COLUMNS($A$1:A1),1) This further reduction seems to work also... =DATE(YEAR($A1),COLUMN(A1),1) Rick |
Date issue to automate calendar - elegance
Or even
=DATE(YEAR($A1),COLUMNS($A$1:A1),1) This further reduction seems to work also... =DATE(YEAR($A1),COLUMN(A1),1) Won't work if you insert a column, that is the whole point of using columns vs column Yes, that is true... but one must wonder how often it will be necessary to protect against the insertion of a new column into a series of 12 columns that span January to December (I would think any such situations would be very rare indeed). Rick |
Date issue to automate calendar - elegance
True
-- Regards, Peo Sjoblom "Rick Rothstein (MVP - VB)" wrote in message ... Or even =DATE(YEAR($A1),COLUMNS($A$1:A1),1) This further reduction seems to work also... =DATE(YEAR($A1),COLUMN(A1),1) Won't work if you insert a column, that is the whole point of using columns vs column Yes, that is true... but one must wonder how often it will be necessary to protect against the insertion of a new column into a series of 12 columns that span January to December (I would think any such situations would be very rare indeed). Rick |
Date issue to automate calendar - elegance
Your formula did not give what Babs wanted. She wanted the dates to run from
Jan to Dec for any input date. Your formula is based on the month of the input date. Look carefully at her original message: I have a start date of 11/1/2008 - What I want to populate is a table that has columns for Jan-08, Feb-08, Mar-08 etc begining with January ending with December. I know I can type the column headers, but I want the column headers to display the correct year if my start date becomes 2/1/2009. Therefore Jan-09, Feb-09, Mar-09 etc. Your formula =DATE(YEAR($A1),MONTH($A1)+COLUMN()-COLUMN($F1),1) depends on the month in the date. Your formula should have been =DATE(YEAR($A1),1+COLUMN()-COLUMN($F1),1) to give her what she wanted. Regards Dave "Chip Pearson" wrote in message ... Nice, but that is not an answer to the question which is why does Chips formula not work. My formula did work as I intended, but Babs entered in down rows in a single column, not across columns in a single row. But as others have shown, mine was not the simplest answer.I think Rick wins the prize for simplicity. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Dave Thomas" wrote in message t... Nice, but that is not an answer to the question which is why does Chips formula not work. "Rick Rothstein (MVP - VB)" wrote in message ... Or even =DATE(YEAR($A1),COLUMNS($A$1:A1),1) This further reduction seems to work also... =DATE(YEAR($A1),COLUMN(A1),1) Rick |
Date issue to automate calendar - elegance
Chips's formula did not give what Babs wanted. She wanted the dates to run
from Jan to Dec for any input date. Chips' formula is based on the month of the input date. Look carefully at her original message: I have a start date of 11/1/2008 - What I want to populate is a table that has columns for Jan-08, Feb-08, Mar-08 etc begining with January ending with December. I know I can type the column headers, but I want the column headers to display the correct year if my start date becomes 2/1/2009. Therefore Jan-09, Feb-09, Mar-09 etc. Chips' formula =DATE(YEAR($A1),MONTH($A1)+COLUMN()-COLUMN($F1),1) depends on the month in the date. Chips' should have been =DATE(YEAR($A1),1+COLUMN()-COLUMN($F1),1) to give her what she wanted. Regards Dave "Rick Rothstein (MVP - VB)" wrote in message ... Maybe I am missing something, but Chip's formula appears to work fine for me as long as it is placed in column F (and once you format the cells to show mmm-yy, of course). Rick "Dave Thomas" wrote in message t... Nice, but that is not an answer to the question which is why does Chips formula not work. "Rick Rothstein (MVP - VB)" wrote in message ... Or even =DATE(YEAR($A1),COLUMNS($A$1:A1),1) This further reduction seems to work also... =DATE(YEAR($A1),COLUMN(A1),1) Rick |
Date issue to automate calendar - elegance
Ah! Yes, but of course.
<LOLThat's what I get for testing it with a starting date in January!</LOL Rick "Dave Thomas" wrote in message et... Chips's formula did not give what Babs wanted. She wanted the dates to run from Jan to Dec for any input date. Chips' formula is based on the month of the input date. Look carefully at her original message: I have a start date of 11/1/2008 - What I want to populate is a table that has columns for Jan-08, Feb-08, Mar-08 etc begining with January ending with December. I know I can type the column headers, but I want the column headers to display the correct year if my start date becomes 2/1/2009. Therefore Jan-09, Feb-09, Mar-09 etc. Chips' formula =DATE(YEAR($A1),MONTH($A1)+COLUMN()-COLUMN($F1),1) depends on the month in the date. Chips' should have been =DATE(YEAR($A1),1+COLUMN()-COLUMN($F1),1) to give her what she wanted. Regards Dave "Rick Rothstein (MVP - VB)" wrote in message ... Maybe I am missing something, but Chip's formula appears to work fine for me as long as it is placed in column F (and once you format the cells to show mmm-yy, of course). Rick "Dave Thomas" wrote in message t... Nice, but that is not an answer to the question which is why does Chips formula not work. "Rick Rothstein (MVP - VB)" wrote in message ... Or even =DATE(YEAR($A1),COLUMNS($A$1:A1),1) This further reduction seems to work also... =DATE(YEAR($A1),COLUMN(A1),1) Rick |
Date issue to automate calendar - So much ado about nothing
Never have I seen so much ado about nothing. hehehe
"Babs in Ohio" wrote in message ... I have a start date of 11/1/2008 - What I want to populate is a table that has columns for Jan-08, Feb-08, Mar-08 etc begining with January ending with December. I know I can type the column headers, but I want the column headers to display the correct year if my start date becomes 2/1/2009. Therefore Jan-09, Feb-09, Mar-09 etc. Is there any formula that I can use? -- Babs |
Date issue to automate calendar - elegance
I misread (actually, didn't thoroughly read) the original question. My
formula correctly did what I *thought* she wanted. Alas, it was no good. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Dave Thomas" wrote in message et... Chips's formula did not give what Babs wanted. She wanted the dates to run from Jan to Dec for any input date. Chips' formula is based on the month of the input date. Look carefully at her original message: I have a start date of 11/1/2008 - What I want to populate is a table that has columns for Jan-08, Feb-08, Mar-08 etc begining with January ending with December. I know I can type the column headers, but I want the column headers to display the correct year if my start date becomes 2/1/2009. Therefore Jan-09, Feb-09, Mar-09 etc. Chips' formula =DATE(YEAR($A1),MONTH($A1)+COLUMN()-COLUMN($F1),1) depends on the month in the date. Chips' should have been =DATE(YEAR($A1),1+COLUMN()-COLUMN($F1),1) to give her what she wanted. Regards Dave "Rick Rothstein (MVP - VB)" wrote in message ... Maybe I am missing something, but Chip's formula appears to work fine for me as long as it is placed in column F (and once you format the cells to show mmm-yy, of course). Rick "Dave Thomas" wrote in message t... Nice, but that is not an answer to the question which is why does Chips formula not work. "Rick Rothstein (MVP - VB)" wrote in message ... Or even =DATE(YEAR($A1),COLUMNS($A$1:A1),1) This further reduction seems to work also... =DATE(YEAR($A1),COLUMN(A1),1) Rick |
Date issue to automate calendar - So much ado about nothing
Thanks to everyone who helped! :-)
-- Babs "Dave Thomas" wrote: Never have I seen so much ado about nothing. hehehe "Babs in Ohio" wrote in message ... I have a start date of 11/1/2008 - What I want to populate is a table that has columns for Jan-08, Feb-08, Mar-08 etc begining with January ending with December. I know I can type the column headers, but I want the column headers to display the correct year if my start date becomes 2/1/2009. Therefore Jan-09, Feb-09, Mar-09 etc. Is there any formula that I can use? -- Babs |
| All times are GMT +1. The time now is 08:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com