Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates - Months & Years
I am looking for a formula or a way to have years & months calculated from a
starting month and year. Here is what I have. Sheet 1 has 2 cells, cell A1 is for picking the starting month (from a drop down list) & cell A2 is to pick the starting year (also from a drop down list). Sheet 2 is set up with a 36 month grid in which estimated hours are input. What I need it to do is automatically calculate the year (in cell row 1) and the month (in cell row 2) based on the selections made on sheet 1. Also, I need it to automatically roll to the next higher year when ever the month "Jan" comes up (see D1,2). I would like it to display year & month as shown here. (A) (B) (C) (D) (1) 2005 2005 2005 2006 and so for a total of 36 months (2) Oct Nov Dec Jan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates - Months & Years
Add these formula on sheet2
A1: =Sheet1!A2 A2: =Sheet1!A1 B1: =IF(A2="Jan",A1+A1,A1) B2: =TEXT(DATE(A1,MONTH(DATEVALUE("01 "&A2&" 2005"))+1,1),"mmm") copy B1:B2 across -- HTH RP (remove nothere from the email address if mailing direct) "Rick" wrote in message ... I am looking for a formula or a way to have years & months calculated from a starting month and year. Here is what I have. Sheet 1 has 2 cells, cell A1 is for picking the starting month (from a drop down list) & cell A2 is to pick the starting year (also from a drop down list). Sheet 2 is set up with a 36 month grid in which estimated hours are input. What I need it to do is automatically calculate the year (in cell row 1) and the month (in cell row 2) based on the selections made on sheet 1. Also, I need it to automatically roll to the next higher year when ever the month "Jan" comes up (see D1,2). I would like it to display year & month as shown here. (A) (B) (C) (D) (1) 2005 2005 2005 2006 and so for a total of 36 months (2) Oct Nov Dec Jan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates - Months & Years
Hi Rick
Why not just enter your start date on Sheet1!A1 as 01/11/05 for example (Nov 05). Then on Sheet2 A1=Sheet1!A1 FormatCellsNumberCustom yyyy B1=DATE(YEAR(A1),MONTH(A1)+1,1) again formatted as yyyy copy across for 35 columns A2=A1 but formatted as mmm Copy A2 across for 36 columns Changing the date in Sheet1!A1 will alter both start year and start month Regards Roger Govier Rick wrote: I am looking for a formula or a way to have years & months calculated from a starting month and year. Here is what I have. Sheet 1 has 2 cells, cell A1 is for picking the starting month (from a drop down list) & cell A2 is to pick the starting year (also from a drop down list). Sheet 2 is set up with a 36 month grid in which estimated hours are input. What I need it to do is automatically calculate the year (in cell row 1) and the month (in cell row 2) based on the selections made on sheet 1. Also, I need it to automatically roll to the next higher year when ever the month "Jan" comes up (see D1,2). I would like it to display year & month as shown here. (A) (B) (C) (D) (1) 2005 2005 2005 2006 and so for a total of 36 months (2) Oct Nov Dec Jan |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates - Months & Years
Hi
Add a sheet p.e. Months, with a single-column table (Month) P.e. into A2 enter the formula =DATE(YEAR(TODAY()),MONTH(TODAY())-3+ROW(),1) , format p.e. as Custom "mmmm yyyy", or any other format you'll like the month displayed in drop-down. (In my example, the list of months starts with pre-previous one - change the constante to modify this) Copy the formula down for number of rows, equal to number of months you want to have in drop-down displayed. Select the range with months, and define it as named range, p.e. as Months. On your sheet, format a single cell (p.e. B1) as data validation list with source =Months , and format this cell in any custom date format you like (p.e. mmmm yyyy, or yyyy mmm, etc.) On sheet 2, you can use formulas like =YEAR(DATE(YEAR(Sheet1!$B$1),MONTH(Sheet1!$B$1)+n, 1)) {or =TEXT(DATE(YEAR(Sheet1!$B$1),MONTH(Sheet1!$B$1)+n, 1),"yyyy")} , and =TEXT(DATE(YEAR(Sheet1!$B$1),MONTH(Sheet1!$B$1)+n, 1),"mmm") , to display the year/month n months later as selected in Sheet1!$B$1 -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Rick" wrote in message ... I am looking for a formula or a way to have years & months calculated from a starting month and year. Here is what I have. Sheet 1 has 2 cells, cell A1 is for picking the starting month (from a drop down list) & cell A2 is to pick the starting year (also from a drop down list). Sheet 2 is set up with a 36 month grid in which estimated hours are input. What I need it to do is automatically calculate the year (in cell row 1) and the month (in cell row 2) based on the selections made on sheet 1. Also, I need it to automatically roll to the next higher year when ever the month "Jan" comes up (see D1,2). I would like it to display year & month as shown here. (A) (B) (C) (D) (1) 2005 2005 2005 2006 and so for a total of 36 months (2) Oct Nov Dec Jan |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates - Months & Years
Roger
Thanks...simple solutions are often the hardest to see. "Roger Govier" wrote: Hi Rick Why not just enter your start date on Sheet1!A1 as 01/11/05 for example (Nov 05). Then on Sheet2 A1=Sheet1!A1 FormatCellsNumberCustom yyyy B1=DATE(YEAR(A1),MONTH(A1)+1,1) again formatted as yyyy copy across for 35 columns A2=A1 but formatted as mmm Copy A2 across for 36 columns Changing the date in Sheet1!A1 will alter both start year and start month Regards Roger Govier Rick wrote: I am looking for a formula or a way to have years & months calculated from a starting month and year. Here is what I have. Sheet 1 has 2 cells, cell A1 is for picking the starting month (from a drop down list) & cell A2 is to pick the starting year (also from a drop down list). Sheet 2 is set up with a 36 month grid in which estimated hours are input. What I need it to do is automatically calculate the year (in cell row 1) and the month (in cell row 2) based on the selections made on sheet 1. Also, I need it to automatically roll to the next higher year when ever the month "Jan" comes up (see D1,2). I would like it to display year & month as shown here. (A) (B) (C) (D) (1) 2005 2005 2005 2006 and so for a total of 36 months (2) Oct Nov Dec Jan |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates - Months & Years
Hi Rick
If only the "trees" would disappear for me all of the time<g You're very welcome. Regards Roger Govier Rick wrote: Roger Thanks...simple solutions are often the hardest to see. "Roger Govier" wrote: Hi Rick Why not just enter your start date on Sheet1!A1 as 01/11/05 for example (Nov 05). Then on Sheet2 A1=Sheet1!A1 FormatCellsNumberCustom yyyy B1=DATE(YEAR(A1),MONTH(A1)+1,1) again formatted as yyyy copy across for 35 columns A2=A1 but formatted as mmm Copy A2 across for 36 columns Changing the date in Sheet1!A1 will alter both start year and start month Regards Roger Govier Rick wrote: I am looking for a formula or a way to have years & months calculated from a starting month and year. Here is what I have. Sheet 1 has 2 cells, cell A1 is for picking the starting month (from a drop down list) & cell A2 is to pick the starting year (also from a drop down list). Sheet 2 is set up with a 36 month grid in which estimated hours are input. What I need it to do is automatically calculate the year (in cell row 1) and the month (in cell row 2) based on the selections made on sheet 1. Also, I need it to automatically roll to the next higher year when ever the month "Jan" comes up (see D1,2). I would like it to display year & month as shown here. (A) (B) (C) (D) (1) 2005 2005 2005 2006 and so for a total of 36 months (2) Oct Nov Dec Jan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i sort dates by months and not years? | New Users to Excel | |||
months between 2 dates!!! | Excel Discussion (Misc queries) | |||
Number of years, months, days between two dates. | Excel Worksheet Functions | |||
difference between two dates in years, months and days. | Excel Worksheet Functions | |||
How do I display months and years between two dates | Excel Discussion (Misc queries) |