Date
 

Hi,

I need help regarding converting the mm/dd/yy to period number. For
example: when I enter the date, it will convert to number (see below)

Date Period #

7/1/2006 1
8/1/2006 2
9/1/2006 3
10/1/2006 4
11/1/2006 5
12/1/2006 6
1/1/2007 7
2/1/2007 8
3/1/2007 9
4/1/2007 10
5/1/2007 11
6/1/2007 12

Can anyone help me? Thanks a lot.

Date
 

Where are you entering the date? Do you want this cell to change to the
number or do you want another cell to give you the corresponding number after
you enter the date?

"momotaro" wrote:

Hi,

I need help regarding converting the mm/dd/yy to period number. For
example: when I enter the date, it will convert to number (see below)

Date Period #

7/1/2006 1
8/1/2006 2
9/1/2006 3
10/1/2006 4
11/1/2006 5
12/1/2006 6
1/1/2007 7
2/1/2007 8
3/1/2007 9
4/1/2007 10
5/1/2007 11
6/1/2007 12

Can anyone help me? Thanks a lot.

Date
 

In the example, for the period you should just enter
=MONTH(A1)-6+12*(YEAR(A1)-2006)

and format the cell as a number with no decimal places.

--
Allllen


"momotaro" wrote:

Hi,

I need help regarding converting the mm/dd/yy to period number. For
example: when I enter the date, it will convert to number (see below)

Date Period #

7/1/2006 1
8/1/2006 2
9/1/2006 3
10/1/2006 4
11/1/2006 5
12/1/2006 6
1/1/2007 7
2/1/2007 8
3/1/2007 9
4/1/2007 10
5/1/2007 11
6/1/2007 12

Can anyone help me? Thanks a lot.

Date
 

A helper cell and 2 nested if statements will work.

"momotaro" wrote:

Hi,

I need help regarding converting the mm/dd/yy to period number. For
example: when I enter the date, it will convert to number (see below)

Date Period #

7/1/2006 1
8/1/2006 2
9/1/2006 3
10/1/2006 4
11/1/2006 5
12/1/2006 6
1/1/2007 7
2/1/2007 8
3/1/2007 9
4/1/2007 10
5/1/2007 11
6/1/2007 12

Can anyone help me? Thanks a lot.

Date
 

Allllen's idea is better than mine. WTG Allllen

"Rookie 1st class" wrote:

A helper cell and 2 nested if statements will work.

"momotaro" wrote:

Hi,

I need help regarding converting the mm/dd/yy to period number. For
example: when I enter the date, it will convert to number (see below)

Date Period #

7/1/2006 1
8/1/2006 2
9/1/2006 3
10/1/2006 4
11/1/2006 5
12/1/2006 6
1/1/2007 7
2/1/2007 8
3/1/2007 9
4/1/2007 10
5/1/2007 11
6/1/2007 12

Can anyone help me? Thanks a lot.

Date
 

Assuming that the dates you've given are in MDY format, and that any other
date in the same month should be given the same period number, then try:
=12*(YEAR(A1)-2006)+MONTH(A1)-6

If you want your period number to reset & go back to 1 in the following
July, rather than going on to 13 and beyond, you can modify the formula
accordingly, such as:
=MOD(12*(YEAR(A4)-2006)+MONTH(A4)-7,12)+1
--
David Biddulph

"momotaro" wrote in message
...
Hi,

I need help regarding converting the mm/dd/yy to period number. For
example: when I enter the date, it will convert to number (see below)

Date Period #

7/1/2006 1
8/1/2006 2
9/1/2006 3
10/1/2006 4
11/1/2006 5
12/1/2006 6
1/1/2007 7
2/1/2007 8
3/1/2007 9
4/1/2007 10
5/1/2007 11
6/1/2007 12

Can anyone help me? Thanks a lot.

Date
 

I want another cell to give me the coressponding number after I enter the
date.

Let say, I enter the date in cell A1 (7/1/06), it will then give me number
"1" in cell A2.

"Chad" wrote:

Where are you entering the date? Do you want this cell to change to the
number or do you want another cell to give you the corresponding number after
you enter the date?

"momotaro" wrote:

Hi,

I need help regarding converting the mm/dd/yy to period number. For
example: when I enter the date, it will convert to number (see below)

Date Period #

7/1/2006 1
8/1/2006 2
9/1/2006 3
10/1/2006 4
11/1/2006 5
12/1/2006 6
1/1/2007 7
2/1/2007 8
3/1/2007 9
4/1/2007 10
5/1/2007 11
6/1/2007 12

Can anyone help me? Thanks a lot.

Date
 

Thanks David.

"David Biddulph" wrote:

Assuming that the dates you've given are in MDY format, and that any other
date in the same month should be given the same period number, then try:
=12*(YEAR(A1)-2006)+MONTH(A1)-6

If you want your period number to reset & go back to 1 in the following
July, rather than going on to 13 and beyond, you can modify the formula
accordingly, such as:
=MOD(12*(YEAR(A4)-2006)+MONTH(A4)-7,12)+1
--
David Biddulph

"momotaro" wrote in message
...
Hi,

I need help regarding converting the mm/dd/yy to period number. For
example: when I enter the date, it will convert to number (see below)

Date Period #

7/1/2006 1
8/1/2006 2
9/1/2006 3
10/1/2006 4
11/1/2006 5
12/1/2006 6
1/1/2007 7
2/1/2007 8
3/1/2007 9
4/1/2007 10
5/1/2007 11
6/1/2007 12

Can anyone help me? Thanks a lot.

Date
 

Format B1 as a number with zero decimals.
Paste this and copy down, It doesn't matter what the year or day.
=IF(MONTH(A1)+6<=12,MONTH(A1)+6,IF(MONTH(A1)+6=13 ,MONTH(A1)-6))
Lou


"momotaro" wrote:


Thanks David.

"David Biddulph" wrote:

Assuming that the dates you've given are in MDY format, and that any other
date in the same month should be given the same period number, then try:
=12*(YEAR(A1)-2006)+MONTH(A1)-6

If you want your period number to reset & go back to 1 in the following
July, rather than going on to 13 and beyond, you can modify the formula
accordingly, such as:
=MOD(12*(YEAR(A4)-2006)+MONTH(A4)-7,12)+1
--
David Biddulph

"momotaro" wrote in message
...
Hi,

I need help regarding converting the mm/dd/yy to period number. For
example: when I enter the date, it will convert to number (see below)

Date Period #

7/1/2006 1
8/1/2006 2
9/1/2006 3
10/1/2006 4
11/1/2006 5
12/1/2006 6
1/1/2007 7
2/1/2007 8
3/1/2007 9
4/1/2007 10
5/1/2007 11
6/1/2007 12

Can anyone help me? Thanks a lot.