Dates - Months & Years

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rick - 23 Nov 2005 14:12 GMT

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 to this Message

Bob Phillips - 23 Nov 2005 14:33 GMT

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

Signature

HTH

RP
(remove nothere from the email address if mailing direct)

> 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.
[quoted text clipped - 13 lines]
> (1) 2005 2005 2005 2006 and so for a total of 36 months
> (2) Oct Nov Dec Jan

Reply to this Message

Roger Govier - 23 Nov 2005 14:54 GMT

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 Format>Cells>Number>Custom> 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

Reply to this Message

Rick - 23 Nov 2005 16:05 GMT

Roger
Thanks...simple solutions are often the hardest to see.

> Hi Rick
>
[quoted text clipped - 31 lines]
> > (1) 2005 2005 2005 2006 and so for a total of 36 months
> > (2) Oct Nov Dec Jan

Reply to this Message

Roger Govier - 23 Nov 2005 16:11 GMT

Hi Rick

If only the "trees" would disappear for me all of the time<g>
You're very welcome.

Regards

Roger Govier

> Roger
> Thanks...simple solutions are often the hardest to see.
[quoted text clipped - 34 lines]
>>>(1) 2005 2005 2005 2006 and so for a total of 36 months
>>>(2) Oct Nov Dec Jan

Reply to this Message

Arvi Laanemets - 23 Nov 2005 15:03 GMT

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

Signature

Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )

>I am looking for a formula or a way to have years & months calculated from
>a
[quoted text clipped - 18 lines]
> (1) 2005 2005 2005 2006 and so for a total of 36 months
> (2) Oct Nov Dec Jan

Reply to this Message