Date

[Guest]

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.

 

[Guest]

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?

 

[Guest]

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.

 

[Guest]

A helper cell and 2 nested if statements will work.

 

[Guest]

Allllen's idea is better than mine. WTG Allllen

A helper cell and 2 nested if statements will work.

 

[David Biddulph]

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

 

[Guest]

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.

 

[Guest]

Thanks David.

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

 

[Guest]

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


: