| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Need help w/dates
i can't figure out how to solve this problem, this is the information i have:
BEGINN ENDING PERIOD Dates 01/01/05 01/31/05 P01-05 07/23/05 01/02/05 28/02/05 P02-05 08/09/05 01/03/05 31/03/05 P03-05 08/08/05 01/04/05 30/04/05 P04-05 08/18/05 01/05/05 31/05/05 P05-05 08/10/05 01/06/05 30/06/05 P06-05 08/12/05 01/07/05 31/07/05 P07-05 08/24/05 01/08/05 31/08/05 P08-05 01/09/05 30/09/05 P09-05 01/10/05 31/20/05 P10-05 01/11/05 30/11/05 P11-05 01/12/05 31/12/05 P12-05 i would like a formula that looking at the "Dates", would place the date in the correct "Period" according to the "beginning" and "Ending" of the chart, can anybody help me please? |
|
#2
|
|||
|
|||
|
i have: BEGINN ENDING PERIOD Dates
01/01/05 01/31/05 P01-05 07/23/05 01/02/05 28/02/05 P02-05 08/09/05 01/03/05 31/03/05 P03-05 08/08/05 01/04/05 30/04/05 P04-05 08/18/05 01/05/05 31/05/05 P05-05 08/10/05 01/06/05 30/06/05 P06-05 08/12/05 01/07/05 31/07/05 P07-05 08/24/05 01/08/05 31/08/05 P08-05 01/09/05 30/09/05 P09-05 01/10/05 31/20/05 P10-05 01/11/05 30/11/05 P11-05 01/12/05 31/12/05 P12-05 i would like a formula that looking at the "Dates", would place the date in the correct "Period" according to the "beginning" and "Ending" of the chart, can anybody help me please? First clean up the date formats. It appears that the beginning and ending dates have different day/month orders. I'm assuming column E is the Dates column. If the ranges are always the calendar months. You can put =E2 in cell F2 and copy down, then select column F use: Format Cells Number Custom Pmm"-"yy Otherwise, you might want to look into the VLOOKUP function. |
|
#3
|
|||
|
|||
|
Jay i think i simplified the problem a little too much, my actual "table of
reference" is this: BEGINN ENDING PERIOD Dates 1/4/2005 2/3/2005 P01-05 7/23/2005 2/4/2005 3/31/2005 P02-05 8/9/2005 4/1/2005 5/5/2005 P03-05 8/8/2005 5/6/2005 6/2/2005 P04-05 8/18/2005 6/3/2005 6/30/2005 P05-05 8/10/2005 7/1/2005 8/4/2005 P06-05 8/12/2005 8/5/2005 9/1/2005 P07-05 8/24/2005 9/2/2005 9/29/2005 P08-05 9/30/2005 10/3/2005 P09-05 10/4/2005 12/1/2005 P10-05 12/2/2005 12/29/2005 P11-05 12/30/2005 2/2/2006 P12-05 so, i don't need just the month, can you please assist? thanks "Jay" wrote: i have: BEGINN ENDING PERIOD Dates 01/01/05 01/31/05 P01-05 07/23/05 01/02/05 28/02/05 P02-05 08/09/05 01/03/05 31/03/05 P03-05 08/08/05 01/04/05 30/04/05 P04-05 08/18/05 01/05/05 31/05/05 P05-05 08/10/05 01/06/05 30/06/05 P06-05 08/12/05 01/07/05 31/07/05 P07-05 08/24/05 01/08/05 31/08/05 P08-05 01/09/05 30/09/05 P09-05 01/10/05 31/20/05 P10-05 01/11/05 30/11/05 P11-05 01/12/05 31/12/05 P12-05 i would like a formula that looking at the "Dates", would place the date in the correct "Period" according to the "beginning" and "Ending" of the chart, can anybody help me please? First clean up the date formats. It appears that the beginning and ending dates have different day/month orders. I'm assuming column E is the Dates column. If the ranges are always the calendar months. You can put =E2 in cell F2 and copy down, then select column F use: Format Cells Number Custom Pmm"-"yy Otherwise, you might want to look into the VLOOKUP function. |
|
#4
|
|||
|
|||
|
Hi Armando
Set up a table as follows:- A1 = 1/4/2005 B1 = P01-05 A2 = 2/3/2005 B2 = P02-05 A3 = 31/3/2005 B3 = P03-05 etc. Mark the range of cells and in the white pane to the left of column A and above Row 1, type Periods and press Enter. This will create a named range Wherever you have entered on your sheet 23/7/2005 , lets say in cell D1, enter in E1 =VLOOKUP(D1,Periods,2) Regards Roger Govier Armando Martellini wrote: Jay i think i simplified the problem a little too much, my actual "table of reference" is this: BEGINN ENDING PERIOD Dates 1/4/2005 2/3/2005 P01-05 7/23/2005 2/4/2005 3/31/2005 P02-05 8/9/2005 4/1/2005 5/5/2005 P03-05 8/8/2005 5/6/2005 6/2/2005 P04-05 8/18/2005 6/3/2005 6/30/2005 P05-05 8/10/2005 7/1/2005 8/4/2005 P06-05 8/12/2005 8/5/2005 9/1/2005 P07-05 8/24/2005 9/2/2005 9/29/2005 P08-05 9/30/2005 10/3/2005 P09-05 10/4/2005 12/1/2005 P10-05 12/2/2005 12/29/2005 P11-05 12/30/2005 2/2/2006 P12-05 so, i don't need just the month, can you please assist? thanks "Jay" wrote: i have: BEGINN ENDING PERIOD Dates 01/01/05 01/31/05 P01-05 07/23/05 01/02/05 28/02/05 P02-05 08/09/05 01/03/05 31/03/05 P03-05 08/08/05 01/04/05 30/04/05 P04-05 08/18/05 01/05/05 31/05/05 P05-05 08/10/05 01/06/05 30/06/05 P06-05 08/12/05 01/07/05 31/07/05 P07-05 08/24/05 01/08/05 31/08/05 P08-05 01/09/05 30/09/05 P09-05 01/10/05 31/20/05 P10-05 01/11/05 30/11/05 P11-05 01/12/05 31/12/05 P12-05 i would like a formula that looking at the "Dates", would place the date in the correct "Period" according to the "beginning" and "Ending" of the chart, can anybody help me please? First clean up the date formats. It appears that the beginning and ending dates have different day/month orders. I'm assuming column E is the Dates column. If the ranges are always the calendar months. You can put =E2 in cell F2 and copy down, then select column F use: Format Cells Number Custom Pmm"-"yy Otherwise, you might want to look into the VLOOKUP function. |
|
#5
|
|||
|
|||
|
Roger, maybe i didn't understand your instructions, but i can't seem to make
it work, in the vlookup you suggested, i can't find any indication that i'm looking for a value in between 2 ranges to get the "period" value. can you advice? Thanks for your help "Roger Govier" wrote: Hi Armando Set up a table as follows:- A1 = 1/4/2005 B1 = P01-05 A2 = 2/3/2005 B2 = P02-05 A3 = 31/3/2005 B3 = P03-05 etc. Mark the range of cells and in the white pane to the left of column A and above Row 1, type Periods and press Enter. This will create a named range Wherever you have entered on your sheet 23/7/2005 , lets say in cell D1, enter in E1 =VLOOKUP(D1,Periods,2) Regards Roger Govier Armando Martellini wrote: Jay i think i simplified the problem a little too much, my actual "table of reference" is this: BEGINN ENDING PERIOD Dates 1/4/2005 2/3/2005 P01-05 7/23/2005 2/4/2005 3/31/2005 P02-05 8/9/2005 4/1/2005 5/5/2005 P03-05 8/8/2005 5/6/2005 6/2/2005 P04-05 8/18/2005 6/3/2005 6/30/2005 P05-05 8/10/2005 7/1/2005 8/4/2005 P06-05 8/12/2005 8/5/2005 9/1/2005 P07-05 8/24/2005 9/2/2005 9/29/2005 P08-05 9/30/2005 10/3/2005 P09-05 10/4/2005 12/1/2005 P10-05 12/2/2005 12/29/2005 P11-05 12/30/2005 2/2/2006 P12-05 so, i don't need just the month, can you please assist? thanks "Jay" wrote: i have: BEGINN ENDING PERIOD Dates 01/01/05 01/31/05 P01-05 07/23/05 01/02/05 28/02/05 P02-05 08/09/05 01/03/05 31/03/05 P03-05 08/08/05 01/04/05 30/04/05 P04-05 08/18/05 01/05/05 31/05/05 P05-05 08/10/05 01/06/05 30/06/05 P06-05 08/12/05 01/07/05 31/07/05 P07-05 08/24/05 01/08/05 31/08/05 P08-05 01/09/05 30/09/05 P09-05 01/10/05 31/20/05 P10-05 01/11/05 30/11/05 P11-05 01/12/05 31/12/05 P12-05 i would like a formula that looking at the "Dates", would place the date in the correct "Period" according to the "beginning" and "Ending" of the chart, can anybody help me please? First clean up the date formats. It appears that the beginning and ending dates have different day/month orders. I'm assuming column E is the Dates column. If the ranges are always the calendar months. You can put =E2 in cell F2 and copy down, then select column F use: Format Cells Number Custom Pmm"-"yy Otherwise, you might want to look into the VLOOKUP function. |
|
#6
|
|||
|
|||
|
Hi Armando
Because we are not using the 4th argument of VLOOKUP, False or 0, then instead of looking for an exact match, Vlookup returns the highest value that does not exceed the lookup value. Therefore with the table as I had set it out, as long as you continued it on downward to give values for all periods through to P12-05, (as below), then with a date to be looked up of 23/7/2005, this is less than 04/08/2005, so it would take the previous value of 30/06/2005 which has P06-05 in the column next to it. i.e. 23/07/2005 is between 30/06/2005 and 04/08/2005 NB Dates here in UK format as opposed to US format as posted to you in my previous response. 04/01/2005 P01-05 03/02/2005 p02-05 31/03/2005 p03-05 05/05/2005 P04-05 02/06/2005 P05-05 30/06/2005 P06-05 04/08/2005 P07-05 01/09/2005 P08-05 29/09/2005 P09-05 03/10/2005 P10-05 01/12/2005 P11-05 29/12/2005 P12-05 02/02/2006 P12-05 Regards Roger Govier Armando Martellini wrote: Roger, maybe i didn't understand your instructions, but i can't seem to make it work, in the vlookup you suggested, i can't find any indication that i'm looking for a value in between 2 ranges to get the "period" value. can you advice? Thanks for your help "Roger Govier" wrote: Hi Armando Set up a table as follows:- A1 = 1/4/2005 B1 = P01-05 A2 = 2/3/2005 B2 = P02-05 A3 = 31/3/2005 B3 = P03-05 etc. Mark the range of cells and in the white pane to the left of column A and above Row 1, type Periods and press Enter. This will create a named range Wherever you have entered on your sheet 23/7/2005 , lets say in cell D1, enter in E1 =VLOOKUP(D1,Periods,2) Regards Roger Govier Armando Martellini wrote: Jay i think i simplified the problem a little too much, my actual "table of reference" is this: BEGINN ENDING PERIOD Dates 1/4/2005 2/3/2005 P01-05 7/23/2005 2/4/2005 3/31/2005 P02-05 8/9/2005 4/1/2005 5/5/2005 P03-05 8/8/2005 5/6/2005 6/2/2005 P04-05 8/18/2005 6/3/2005 6/30/2005 P05-05 8/10/2005 7/1/2005 8/4/2005 P06-05 8/12/2005 8/5/2005 9/1/2005 P07-05 8/24/2005 9/2/2005 9/29/2005 P08-05 9/30/2005 10/3/2005 P09-05 10/4/2005 12/1/2005 P10-05 12/2/2005 12/29/2005 P11-05 12/30/2005 2/2/2006 P12-05 so, i don't need just the month, can you please assist? thanks "Jay" wrote: i have: BEGINN ENDING PERIOD Dates 01/01/05 01/31/05 P01-05 07/23/05 01/02/05 28/02/05 P02-05 08/09/05 01/03/05 31/03/05 P03-05 08/08/05 01/04/05 30/04/05 P04-05 08/18/05 01/05/05 31/05/05 P05-05 08/10/05 01/06/05 30/06/05 P06-05 08/12/05 01/07/05 31/07/05 P07-05 08/24/05 01/08/05 31/08/05 P08-05 01/09/05 30/09/05 P09-05 01/10/05 31/20/05 P10-05 01/11/05 30/11/05 P11-05 01/12/05 31/12/05 P12-05 i would like a formula that looking at the "Dates", would place the date in the correct "Period" according to the "beginning" and "Ending" of the chart, can anybody help me please? First clean up the date formats. It appears that the beginning and ending dates have different day/month orders. I'm assuming column E is the Dates column. If the ranges are always the calendar months. You can put =E2 in cell F2 and copy down, then select column F use: Format Cells Number Custom Pmm"-"yy Otherwise, you might want to look into the VLOOKUP function. |
|
#7
|
|||
|
|||
|
|
|
#9
|
|||
|
|||
|
Sorry Armando
Hit the send button before posting the revised formula =IF(D1="","",VLOOKUP(D1,Periods,2)) Regards Roger Govier Roger Govier wrote: Hi Armando Yes I did try it and it works. I can send you a file direct if you let me have you email address. #Name suggests you haven't managed to set up the named range correctly. Try InsertNameDefine and in the top Name pane type Periods, and in the lower pane Refers to type =$A$1:$B$13 You would get a #N/A result if there is no data in D1 or any other cell being used as the lookup. This can be overcome by modifying the formula I gave as follows Regards Roger Govier Armando wrote: Roger, i did exactly as you said but the formula returns a #REF value or a #NAME (depending on how i try to "move around" the information. have you tried the formula on a spreadsheet? if so would you be so kind to send it to me, i can't figure out what i'm doing wrong. my e-mail address is . Thanks (again) "Roger Govier" wrote: Hi Armando Because we are not using the 4th argument of VLOOKUP, False or 0, then instead of looking for an exact match, Vlookup returns the highest value that does not exceed the lookup value. Therefore with the table as I had set it out, as long as you continued it on downward to give values for all periods through to P12-05, (as below), then with a date to be looked up of 23/7/2005, this is less than 04/08/2005, so it would take the previous value of 30/06/2005 which has P06-05 in the column next to it. i.e. 23/07/2005 is between 30/06/2005 and 04/08/2005 NB Dates here in UK format as opposed to US format as posted to you in my previous response. 04/01/2005 P01-05 03/02/2005 p02-05 31/03/2005 p03-05 05/05/2005 P04-05 02/06/2005 P05-05 30/06/2005 P06-05 04/08/2005 P07-05 01/09/2005 P08-05 29/09/2005 P09-05 03/10/2005 P10-05 01/12/2005 P11-05 29/12/2005 P12-05 02/02/2006 P12-05 Regards Roger Govier Armando Martellini wrote: Roger, maybe i didn't understand your instructions, but i can't seem to make it work, in the vlookup you suggested, i can't find any indication that i'm looking for a value in between 2 ranges to get the "period" value. can you advice? Thanks for your help "Roger Govier" wrote: Hi Armando Set up a table as follows:- A1 = 1/4/2005 B1 = P01-05 A2 = 2/3/2005 B2 = P02-05 A3 = 31/3/2005 B3 = P03-05 etc. Mark the range of cells and in the white pane to the left of column A and above Row 1, type Periods and press Enter. This will create a named range Wherever you have entered on your sheet 23/7/2005 , lets say in cell D1, enter in E1 =VLOOKUP(D1,Periods,2) Regards Roger Govier Armando Martellini wrote: Jay i think i simplified the problem a little too much, my actual "table of reference" is this: BEGINN ENDING PERIOD Dates 1/4/2005 2/3/2005 P01-05 7/23/2005 2/4/2005 3/31/2005 P02-05 8/9/2005 4/1/2005 5/5/2005 P03-05 8/8/2005 5/6/2005 6/2/2005 P04-05 8/18/2005 6/3/2005 6/30/2005 P05-05 8/10/2005 7/1/2005 8/4/2005 P06-05 8/12/2005 8/5/2005 9/1/2005 P07-05 8/24/2005 9/2/2005 9/29/2005 P08-05 9/30/2005 10/3/2005 P09-05 10/4/2005 12/1/2005 P10-05 12/2/2005 12/29/2005 P11-05 12/30/2005 2/2/2006 P12-05 so, i don't need just the month, can you please assist? thanks "Jay" wrote: i have: BEGINN ENDING PERIOD Dates 01/01/05 01/31/05 P01-05 07/23/05 01/02/05 28/02/05 P02-05 08/09/05 01/03/05 31/03/05 P03-05 08/08/05 01/04/05 30/04/05 P04-05 08/18/05 01/05/05 31/05/05 P05-05 08/10/05 01/06/05 30/06/05 P06-05 08/12/05 01/07/05 31/07/05 P07-05 08/24/05 01/08/05 31/08/05 P08-05 01/09/05 30/09/05 P09-05 01/10/05 31/20/05 P10-05 01/11/05 30/11/05 P11-05 01/12/05 31/12/05 P12-05 i would like a formula that looking at the "Dates", would place the date in the correct "Period" according to the "beginning" and "Ending" of the chart, can anybody help me please? First clean up the date formats. It appears that the beginning and ending dates have different day/month orders. I'm assuming column E is the Dates column. If the ranges are always the calendar months. You can put =E2 in cell F2 and copy down, then select column F use: Format Cells Number Custom Pmm"-"yy Otherwise, you might want to look into the VLOOKUP function. |
|
#10
|
|||
|
|||
|
Roger thank you again for your patience, i tried everything and verified that
my range was correct, but i can't seem to make it work, the result i get is 38568 which doesn't make any sense. can you please send me the spreadsheet you created my address is "Roger Govier" wrote: Sorry Armando Hit the send button before posting the revised formula =IF(D1="","",VLOOKUP(D1,Periods,2)) Regards Roger Govier Roger Govier wrote: Hi Armando Yes I did try it and it works. I can send you a file direct if you let me have you email address. #Name suggests you haven't managed to set up the named range correctly. Try InsertNameDefine and in the top Name pane type Periods, and in the lower pane Refers to type =$A$1:$B$13 You would get a #N/A result if there is no data in D1 or any other cell being used as the lookup. This can be overcome by modifying the formula I gave as follows Regards Roger Govier Armando wrote: Roger, i did exactly as you said but the formula returns a #REF value or a #NAME (depending on how i try to "move around" the information. have you tried the formula on a spreadsheet? if so would you be so kind to send it to me, i can't figure out what i'm doing wrong. my e-mail address is . Thanks (again) "Roger Govier" wrote: Hi Armando Because we are not using the 4th argument of VLOOKUP, False or 0, then instead of looking for an exact match, Vlookup returns the highest value that does not exceed the lookup value. Therefore with the table as I had set it out, as long as you continued it on downward to give values for all periods through to P12-05, (as below), then with a date to be looked up of 23/7/2005, this is less than 04/08/2005, so it would take the previous value of 30/06/2005 which has P06-05 in the column next to it. i.e. 23/07/2005 is between 30/06/2005 and 04/08/2005 NB Dates here in UK format as opposed to US format as posted to you in my previous response. 04/01/2005 P01-05 03/02/2005 p02-05 31/03/2005 p03-05 05/05/2005 P04-05 02/06/2005 P05-05 30/06/2005 P06-05 04/08/2005 P07-05 01/09/2005 P08-05 29/09/2005 P09-05 03/10/2005 P10-05 01/12/2005 P11-05 29/12/2005 P12-05 02/02/2006 P12-05 Regards Roger Govier Armando Martellini wrote: Roger, maybe i didn't understand your instructions, but i can't seem to make it work, in the vlookup you suggested, i can't find any indication that i'm looking for a value in between 2 ranges to get the "period" value. can you advice? Thanks for your help "Roger Govier" wrote: Hi Armando Set up a table as follows:- A1 = 1/4/2005 B1 = P01-05 A2 = 2/3/2005 B2 = P02-05 A3 = 31/3/2005 B3 = P03-05 etc. Mark the range of cells and in the white pane to the left of column A and above Row 1, type Periods and press Enter. This will create a named range Wherever you have entered on your sheet 23/7/2005 , lets say in cell D1, enter in E1 =VLOOKUP(D1,Periods,2) Regards Roger Govier Armando Martellini wrote: Jay i think i simplified the problem a little too much, my actual "table of reference" is this: BEGINN ENDING PERIOD Dates 1/4/2005 2/3/2005 P01-05 7/23/2005 2/4/2005 3/31/2005 P02-05 8/9/2005 4/1/2005 5/5/2005 P03-05 8/8/2005 5/6/2005 6/2/2005 P04-05 8/18/2005 6/3/2005 6/30/2005 P05-05 8/10/2005 7/1/2005 8/4/2005 P06-05 8/12/2005 8/5/2005 9/1/2005 P07-05 8/24/2005 9/2/2005 9/29/2005 P08-05 9/30/2005 10/3/2005 P09-05 10/4/2005 12/1/2005 P10-05 12/2/2005 12/29/2005 P11-05 12/30/2005 2/2/2006 P12-05 so, i don't need just the month, can you please assist? thanks "Jay" wrote: i have: BEGINN ENDING PERIOD Dates 01/01/05 01/31/05 P01-05 07/23/05 01/02/05 28/02/05 P02-05 08/09/05 01/03/05 31/03/05 P03-05 08/08/05 01/04/05 30/04/05 P04-05 08/18/05 01/05/05 31/05/05 P05-05 08/10/05 01/06/05 30/06/05 P06-05 08/12/05 01/07/05 31/07/05 P07-05 08/24/05 01/08/05 31/08/05 P08-05 01/09/05 30/09/05 P09-05 01/10/05 31/20/05 P10-05 01/11/05 30/11/05 P11-05 01/12/05 31/12/05 P12-05 i would like a formula that looking at the "Dates", would place the date in the correct "Period" according to the "beginning" and "Ending" of the chart, can anybody help me please? First clean up the date formats. It appears that the beginning and ending dates have different day/month orders. I'm assuming column E is the Dates column. If the ranges are always the calendar months. You can put =E2 in cell F2 and copy down, then select column F use: Format Cells Number Custom Pmm"-"yy Otherwise, you might want to look into the VLOOKUP function. |
|
#11
|
|||
|
|||
|
Armando
38568 is the serial number of August 4th, 2005 when formatted as a date. See Chip Pearson's site for explanation of date serial numbers. http://www.cpearson.com/excel/datetime.htm#AddingDates Gord Dibben Excel MVP On Thu, 29 Sep 2005 12:43:02 -0700, Armando Martellini wrote: Roger thank you again for your patience, i tried everything and verified that my range was correct, but i can't seem to make it work, the result i get is 38568 which doesn't make any sense. can you please send me the spreadsheet you created my address is "Roger Govier" wrote: Sorry Armando Hit the send button before posting the revised formula =IF(D1="","",VLOOKUP(D1,Periods,2)) Regards Roger Govier Roger Govier wrote: Hi Armando Yes I did try it and it works. I can send you a file direct if you let me have you email address. #Name suggests you haven't managed to set up the named range correctly. Try InsertNameDefine and in the top Name pane type Periods, and in the lower pane Refers to type =$A$1:$B$13 You would get a #N/A result if there is no data in D1 or any other cell being used as the lookup. This can be overcome by modifying the formula I gave as follows Regards Roger Govier Armando wrote: Roger, i did exactly as you said but the formula returns a #REF value or a #NAME (depending on how i try to "move around" the information. have you tried the formula on a spreadsheet? if so would you be so kind to send it to me, i can't figure out what i'm doing wrong. my e-mail address is . Thanks (again) "Roger Govier" wrote: Hi Armando Because we are not using the 4th argument of VLOOKUP, False or 0, then instead of looking for an exact match, Vlookup returns the highest value that does not exceed the lookup value. Therefore with the table as I had set it out, as long as you continued it on downward to give values for all periods through to P12-05, (as below), then with a date to be looked up of 23/7/2005, this is less than 04/08/2005, so it would take the previous value of 30/06/2005 which has P06-05 in the column next to it. i.e. 23/07/2005 is between 30/06/2005 and 04/08/2005 NB Dates here in UK format as opposed to US format as posted to you in my previous response. 04/01/2005 P01-05 03/02/2005 p02-05 31/03/2005 p03-05 05/05/2005 P04-05 02/06/2005 P05-05 30/06/2005 P06-05 04/08/2005 P07-05 01/09/2005 P08-05 29/09/2005 P09-05 03/10/2005 P10-05 01/12/2005 P11-05 29/12/2005 P12-05 02/02/2006 P12-05 Regards Roger Govier Armando Martellini wrote: Roger, maybe i didn't understand your instructions, but i can't seem to make it work, in the vlookup you suggested, i can't find any indication that i'm looking for a value in between 2 ranges to get the "period" value. can you advice? Thanks for your help "Roger Govier" wrote: Hi Armando Set up a table as follows:- A1 = 1/4/2005 B1 = P01-05 A2 = 2/3/2005 B2 = P02-05 A3 = 31/3/2005 B3 = P03-05 etc. Mark the range of cells and in the white pane to the left of column A and above Row 1, type Periods and press Enter. This will create a named range Wherever you have entered on your sheet 23/7/2005 , lets say in cell D1, enter in E1 =VLOOKUP(D1,Periods,2) Regards Roger Govier Armando Martellini wrote: Jay i think i simplified the problem a little too much, my actual "table of reference" is this: BEGINN ENDING PERIOD Dates 1/4/2005 2/3/2005 P01-05 7/23/2005 2/4/2005 3/31/2005 P02-05 8/9/2005 4/1/2005 5/5/2005 P03-05 8/8/2005 5/6/2005 6/2/2005 P04-05 8/18/2005 6/3/2005 6/30/2005 P05-05 8/10/2005 7/1/2005 8/4/2005 P06-05 8/12/2005 8/5/2005 9/1/2005 P07-05 8/24/2005 9/2/2005 9/29/2005 P08-05 9/30/2005 10/3/2005 P09-05 10/4/2005 12/1/2005 P10-05 12/2/2005 12/29/2005 P11-05 12/30/2005 2/2/2006 P12-05 so, i don't need just the month, can you please assist? thanks "Jay" wrote: i have: BEGINN ENDING PERIOD Dates 01/01/05 01/31/05 P01-05 07/23/05 01/02/05 28/02/05 P02-05 08/09/05 01/03/05 31/03/05 P03-05 08/08/05 01/04/05 30/04/05 P04-05 08/18/05 01/05/05 31/05/05 P05-05 08/10/05 01/06/05 30/06/05 P06-05 08/12/05 01/07/05 31/07/05 P07-05 08/24/05 01/08/05 31/08/05 P08-05 01/09/05 30/09/05 P09-05 01/10/05 31/20/05 P10-05 01/11/05 30/11/05 P11-05 01/12/05 31/12/05 P12-05 i would like a formula that looking at the "Dates", would place the date in the correct "Period" according to the "beginning" and "Ending" of the chart, can anybody help me please? First clean up the date formats. It appears that the beginning and ending dates have different day/month orders. I'm assuming column E is the Dates column. If the ranges are always the calendar months. You can put =E2 in cell F2 and copy down, then select column F use: Format Cells Number Custom Pmm"-"yy Otherwise, you might want to look into the VLOOKUP function. |
|
#12
|
|||
|
|||
|
Hi Armando
Working file sent to you. As Gord pointed out, the result you were getting was the serial date for 4th August 2005. However, you should have got P07-05 which is the correct period, so your VLOOKUP is picking up one column too few. The offset needs to be one greater in the formula, which either means you have not set up your table the same as me, or you did not copy the formula. Anyway, take a look at the file I have sent you. Regards Roger Govier Armando Martellini wrote: Roger thank you again for your patience, i tried everything and verified that my range was correct, but i can't seem to make it work, the result i get is 38568 which doesn't make any sense. can you please send me the spreadsheet you created my address is "Roger Govier" wrote: Sorry Armando Hit the send button before posting the revised formula =IF(D1="","",VLOOKUP(D1,Periods,2)) Regards Roger Govier Roger Govier wrote: Hi Armando Yes I did try it and it works. I can send you a file direct if you let me have you email address. #Name suggests you haven't managed to set up the named range correctly. Try InsertNameDefine and in the top Name pane type Periods, and in the lower pane Refers to type =$A$1:$B$13 You would get a #N/A result if there is no data in D1 or any other cell being used as the lookup. This can be overcome by modifying the formula I gave as follows Regards Roger Govier Armando wrote: Roger, i did exactly as you said but the formula returns a #REF value or a #NAME (depending on how i try to "move around" the information. have you tried the formula on a spreadsheet? if so would you be so kind to send it to me, i can't figure out what i'm doing wrong. my e-mail address is . Thanks (again) "Roger Govier" wrote: Hi Armando Because we are not using the 4th argument of VLOOKUP, False or 0, then instead of looking for an exact match, Vlookup returns the highest value that does not exceed the lookup value. Therefore with the table as I had set it out, as long as you continued it on downward to give values for all periods through to P12-05, (as below), then with a date to be looked up of 23/7/2005, this is less than 04/08/2005, so it would take the previous value of 30/06/2005 which has P06-05 in the column next to it. i.e. 23/07/2005 is between 30/06/2005 and 04/08/2005 NB Dates here in UK format as opposed to US format as posted to you in my previous response. 04/01/2005 P01-05 03/02/2005 p02-05 31/03/2005 p03-05 05/05/2005 P04-05 02/06/2005 P05-05 30/06/2005 P06-05 04/08/2005 P07-05 01/09/2005 P08-05 29/09/2005 P09-05 03/10/2005 P10-05 01/12/2005 P11-05 29/12/2005 P12-05 02/02/2006 P12-05 Regards Roger Govier Armando Martellini wrote: Roger, maybe i didn't understand your instructions, but i can't seem to make it work, in the vlookup you suggested, i can't find any indication that i'm looking for a value in between 2 ranges to get the "period" value. can you advice? Thanks for your help "Roger Govier" wrote: Hi Armando Set up a table as follows:- A1 = 1/4/2005 B1 = P01-05 A2 = 2/3/2005 B2 = P02-05 A3 = 31/3/2005 B3 = P03-05 etc. Mark the range of cells and in the white pane to the left of column A and above Row 1, type Periods and press Enter. This will create a named range Wherever you have entered on your sheet 23/7/2005 , lets say in cell D1, enter in E1 =VLOOKUP(D1,Periods,2) Regards Roger Govier Armando Martellini wrote: Jay i think i simplified the problem a little too much, my actual "table of reference" is this: BEGINN ENDING PERIOD Dates 1/4/2005 2/3/2005 P01-05 7/23/2005 2/4/2005 3/31/2005 P02-05 8/9/2005 4/1/2005 5/5/2005 P03-05 8/8/2005 5/6/2005 6/2/2005 P04-05 8/18/2005 6/3/2005 6/30/2005 P05-05 8/10/2005 7/1/2005 8/4/2005 P06-05 8/12/2005 8/5/2005 9/1/2005 P07-05 8/24/2005 9/2/2005 9/29/2005 P08-05 9/30/2005 10/3/2005 P09-05 10/4/2005 12/1/2005 P10-05 12/2/2005 12/29/2005 P11-05 12/30/2005 2/2/2006 P12-05 so, i don't need just the month, can you please assist? thanks "Jay" wrote: i have: BEGINN ENDING PERIOD Dates 01/01/05 01/31/05 P01-05 07/23/05 01/02/05 28/02/05 P02-05 08/09/05 01/03/05 31/03/05 P03-05 08/08/05 01/04/05 30/04/05 P04-05 08/18/05 01/05/05 31/05/05 P05-05 08/10/05 01/06/05 30/06/05 P06-05 08/12/05 01/07/05 31/07/05 P07-05 08/24/05 01/08/05 31/08/05 P08-05 01/09/05 30/09/05 P09-05 01/10/05 31/20/05 P10-05 01/11/05 30/11/05 P11-05 01/12/05 31/12/05 P12-05 i would like a formula that looking at the "Dates", would place the date in the correct "Period" according to the "beginning" and "Ending" of the chart, can anybody help me please? First clean up the date formats. It appears that the beginning and ending dates have different day/month orders. I'm assuming column E is the Dates column. If the ranges are always the calendar months. You can put =E2 in cell F2 and copy down, then select column F use: Format Cells Number Custom Pmm"-"yy Otherwise, you might want to look into the VLOOKUP function. |
|
#13
|
|||
|
|||
|
Roger, thank you very much for your help, i did everything correctly but
canceling the column with the information i didn't need (i just Hid it). now it works perfectly, but i have another question how can i do a vlookup using the "periods" information, when i try it gives me a #REF because it sees the result as a formula instead of just "PXX". if you don't have time i would understand, you solved my biggest problem. thanks again "Roger Govier" wrote: Hi Armando Working file sent to you. As Gord pointed out, the result you were getting was the serial date for 4th August 2005. However, you should have got P07-05 which is the correct period, so your VLOOKUP is picking up one column too few. The offset needs to be one greater in the formula, which either means you have not set up your table the same as me, or you did not copy the formula. Anyway, take a look at the file I have sent you. Regards Roger Govier Armando Martellini wrote: Roger thank you again for your patience, i tried everything and verified that my range was correct, but i can't seem to make it work, the result i get is 38568 which doesn't make any sense. can you please send me the spreadsheet you created my address is "Roger Govier" wrote: Sorry Armando Hit the send button before posting the revised formula =IF(D1="","",VLOOKUP(D1,Periods,2)) Regards Roger Govier Roger Govier wrote: Hi Armando Yes I did try it and it works. I can send you a file direct if you let me have you email address. #Name suggests you haven't managed to set up the named range correctly. Try InsertNameDefine and in the top Name pane type Periods, and in the lower pane Refers to type =$A$1:$B$13 You would get a #N/A result if there is no data in D1 or any other cell being used as the lookup. This can be overcome by modifying the formula I gave as follows Regards Roger Govier Armando wrote: Roger, i did exactly as you said but the formula returns a #REF value or a #NAME (depending on how i try to "move around" the information. have you tried the formula on a spreadsheet? if so would you be so kind to send it to me, i can't figure out what i'm doing wrong. my e-mail address is . Thanks (again) "Roger Govier" wrote: Hi Armando Because we are not using the 4th argument of VLOOKUP, False or 0, then instead of looking for an exact match, Vlookup returns the highest value that does not exceed the lookup value. Therefore with the table as I had set it out, as long as you continued it on downward to give values for all periods through to P12-05, (as below), then with a date to be looked up of 23/7/2005, this is less than 04/08/2005, so it would take the previous value of 30/06/2005 which has P06-05 in the column next to it. i.e. 23/07/2005 is between 30/06/2005 and 04/08/2005 NB Dates here in UK format as opposed to US format as posted to you in my previous response. 04/01/2005 P01-05 03/02/2005 p02-05 31/03/2005 p03-05 05/05/2005 P04-05 02/06/2005 P05-05 30/06/2005 P06-05 04/08/2005 P07-05 01/09/2005 P08-05 29/09/2005 P09-05 03/10/2005 P10-05 01/12/2005 P11-05 29/12/2005 P12-05 02/02/2006 P12-05 Regards Roger Govier Armando Martellini wrote: Roger, maybe i didn't understand your instructions, but i can't seem to make it work, in the vlookup you suggested, i can't find any indication that i'm looking for a value in between 2 ranges to get the "period" value. can you advice? Thanks for your help "Roger Govier" wrote: Hi Armando Set up a table as follows:- A1 = 1/4/2005 B1 = P01-05 A2 = 2/3/2005 B2 = P02-05 A3 = 31/3/2005 B3 = P03-05 etc. Mark the range of cells and in the white pane to the left of column A and above Row 1, type Periods and press Enter. This will create a named range Wherever you have entered on your sheet 23/7/2005 , lets say in cell D1, enter in E1 =VLOOKUP(D1,Periods,2) Regards Roger Govier Armando Martellini wrote: Jay i think i simplified the problem a little too much, my actual "table of reference" is this: BEGINN ENDING PERIOD Dates 1/4/2005 2/3/2005 P01-05 7/23/2005 2/4/2005 3/31/2005 P02-05 8/9/2005 4/1/2005 5/5/2005 P03-05 8/8/2005 5/6/2005 6/2/2005 P04-05 8/18/2005 6/3/2005 6/30/2005 P05-05 8/10/2005 7/1/2005 8/4/2005 P06-05 8/12/2005 8/5/2005 9/1/2005 P07-05 8/24/2005 9/2/2005 9/29/2005 P08-05 9/30/2005 10/3/2005 P09-05 10/4/2005 12/1/2005 P10-05 12/2/2005 12/29/2005 P11-05 12/30/2005 2/2/2006 P12-05 so, i don't need just the month, can you please assist? thanks "Jay" wrote: i have: BEGINN ENDING PERIOD Dates 01/01/05 01/31/05 P01-05 07/23/05 01/02/05 28/02/05 P02-05 08/09/05 01/03/05 31/03/05 P03-05 08/08/05 01/04/05 30/04/05 P04-05 08/18/05 01/05/05 31/05/05 P05-05 08/10/05 01/06/05 30/06/05 P06-05 08/12/05 01/07/05 31/07/05 P07-05 08/24/05 01/08/05 31/08/05 P08-05 01/09/05 30/09/05 P09-05 01/10/05 31/20/05 P10-05 01/11/05 30/11/05 P11-05 01/12/05 31/12/05 P12-05 i would like a formula that looking at the "Dates", would place the date in the correct "Period" according to the "beginning" and "Ending" of the chart, can anybody help me please? First clean up the date formats. It appears that the beginning and ending dates have different day/month orders. I'm assuming column E is the Dates column. If the ranges are always the calendar months. You can put =E2 in cell F2 and copy down, then select column F use: Format Cells Number Custom Pmm"-"yy Otherwise, you might want to look into the VLOOKUP function. |
|
#14
|
|||
|
|||
|
Hi Armando
I'm not sure what you are trying to do. Mail me back your sheet with a description of what you are trying to do and I will see if I can sort it out. Regards Roger Govier Armando Martellini wrote: Roger, thank you very much for your help, i did everything correctly but canceling the column with the information i didn't need (i just Hid it). now it works perfectly, but i have another question how can i do a vlookup using the "periods" information, when i try it gives me a #REF because it sees the result as a formula instead of just "PXX". if you don't have time i would understand, you solved my biggest problem. thanks again "Roger Govier" wrote: Hi Armando Working file sent to you. As Gord pointed out, the result you were getting was the serial date for 4th August 2005. However, you should have got P07-05 which is the correct period, so your VLOOKUP is picking up one column too few. The offset needs to be one greater in the formula, which either means you have not set up your table the same as me, or you did not copy the formula. Anyway, take a look at the file I have sent you. Regards Roger Govier Armando Martellini wrote: Roger thank you again for your patience, i tried everything and verified that my range was correct, but i can't seem to make it work, the result i get is 38568 which doesn't make any sense. can you please send me the spreadsheet you created my address is "Roger Govier" wrote: Sorry Armando Hit the send button before posting the revised formula =IF(D1="","",VLOOKUP(D1,Periods,2)) Regards Roger Govier Roger Govier wrote: Hi Armando Yes I did try it and it works. I can send you a file direct if you let me have you email address. #Name suggests you haven't managed to set up the named range correctly. Try InsertNameDefine and in the top Name pane type Periods, and in the lower pane Refers to type =$A$1:$B$13 You would get a #N/A result if there is no data in D1 or any other cell being used as the lookup. This can be overcome by modifying the formula I gave as follows Regards Roger Govier Armando wrote: Roger, i did exactly as you said but the formula returns a #REF value or a #NAME (depending on how i try to "move around" the information. have you tried the formula on a spreadsheet? if so would you be so kind to send it to me, i can't figure out what i'm doing wrong. my e-mail address is . Thanks (again) "Roger Govier" wrote: Hi Armando Because we are not using the 4th argument of VLOOKUP, False or 0, then instead of looking for an exact match, Vlookup returns the highest value that does not exceed the lookup value. Therefore with the table as I had set it out, as long as you continued it on downward to give values for all periods through to P12-05, (as below), then with a date to be looked up of 23/7/2005, this is less than 04/08/2005, so it would take the previous value of 30/06/2005 which has P06-05 in the column next to it. i.e. 23/07/2005 is between 30/06/2005 and 04/08/2005 NB Dates here in UK format as opposed to US format as posted to you in my previous response. 04/01/2005 P01-05 03/02/2005 p02-05 31/03/2005 p03-05 05/05/2005 P04-05 02/06/2005 P05-05 30/06/2005 P06-05 04/08/2005 P07-05 01/09/2005 P08-05 29/09/2005 P09-05 03/10/2005 P10-05 01/12/2005 P11-05 29/12/2005 P12-05 02/02/2006 P12-05 Regards Roger Govier Armando Martellini wrote: Roger, maybe i didn't understand your instructions, but i can't seem to make it work, in the vlookup you suggested, i can't find any indication that i'm looking for a value in between 2 ranges to get the "period" value. can you advice? Thanks for your help "Roger Govier" wrote: Hi Armando Set up a table as follows:- A1 = 1/4/2005 B1 = P01-05 A2 = 2/3/2005 B2 = P02-05 A3 = 31/3/2005 B3 = P03-05 etc. Mark the range of cells and in the white pane to the left of column A and above Row 1, type Periods and press Enter. This will create a named range Wherever you have entered on your sheet 23/7/2005 , lets say in cell D1, enter in E1 =VLOOKUP(D1,Periods,2) Regards Roger Govier Armando Martellini wrote: Jay i think i simplified the problem a little too much, my actual "table of reference" is this: BEGINN ENDING PERIOD Dates 1/4/2005 2/3/2005 P01-05 7/23/2005 2/4/2005 3/31/2005 P02-05 8/9/2005 4/1/2005 5/5/2005 P03-05 8/8/2005 5/6/2005 6/2/2005 P04-05 8/18/2005 6/3/2005 6/30/2005 P05-05 8/10/2005 7/1/2005 8/4/2005 P06-05 8/12/2005 8/5/2005 9/1/2005 P07-05 8/24/2005 9/2/2005 9/29/2005 P08-05 9/30/2005 10/3/2005 P09-05 10/4/2005 12/1/2005 P10-05 12/2/2005 12/29/2005 P11-05 12/30/2005 2/2/2006 P12-05 so, i don't need just the month, can you please assist? thanks "Jay" wrote: i have: BEGINN ENDING PERIOD Dates 01/01/05 01/31/05 P01-05 07/23/05 01/02/05 28/02/05 P02-05 08/09/05 01/03/05 31/03/05 P03-05 08/08/05 01/04/05 30/04/05 P04-05 08/18/05 01/05/05 31/05/05 P05-05 08/10/05 01/06/05 30/06/05 P06-05 08/12/05 01/07/05 31/07/05 P07-05 08/24/05 01/08/05 31/08/05 P08-05 01/09/05 30/09/05 P09-05 01/10/05 31/20/05 P10-05 01/11/05 30/11/05 P11-05 01/12/05 31/12/05 P12-05 i would like a formula that looking at the "Dates", would place the date in the correct "Period" according to the "beginning" and "Ending" of the chart, can anybody help me please? First clean up the date formats. It appears that the beginning and ending dates have different day/month orders. I'm assuming column E is the Dates column. If the ranges are always the calendar months. You can put =E2 in cell F2 and copy down, then select column F use: Format Cells Number Custom Pmm"-"yy Otherwise, you might want to look into the VLOOKUP function. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
