Date

M

momotaro

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.
 
C

Chad

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?
 
A

Allllen

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.
 
D

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
 
M

momotaro

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.
 
M

momotaro

Thanks David.

David Biddulph said:
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
Click to expand...
 
R

Rookie 1st class

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


:
 
Top