Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert date | Excel Worksheet Functions | |||
Insert Automatic, Non-Updating Date Stamp | Excel Discussion (Misc queries) | |||
Calculate 1st of month date from existing date. | Excel Discussion (Misc queries) | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Another Date issue. | Excel Worksheet Functions |