| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to link an Excel file due date to Outlook calendar date? | New Users to Excel | |||
| automate calendar dates on worksheets to make a yearly planner | Excel Discussion (Misc queries) | |||
| Calendar issue | Excel Discussion (Misc queries) | |||
| Automate a date referenced in a formula pointing to another sheet | Excel Worksheet Functions | |||
| How to automate footers (filename/date) for all new spreadsheets? | Excel Worksheet Functions | |||
Hybrid Mode
