Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Should be easy, but...
OK.
I've got a list of dates expressed as text: January 2006 February 2006 .... .... .... December 2007 I've then got a cell containing the curret reporting period, expressed as text, February 2007 I'm looking for a formula which will look up the current reporting period within my list of dates and return the text date above it. Ultimately, I'll then use a VLookup to return the adjacent data, but I can manage that without too much help. I know this should be easy, but I've been messing about with this for an hour or so now, so... Any help appreciated. Tom. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Should be easy, but...
Hi
I'm not sure exactly what you are trying to achieve but if you have a Text date of say January 2006 in say cell A1 =A1+0 will turn that into a serial date If you then format that cell FormatCellsNumberCustom dd/mmm/yy you will get 01/Jan/2006 Not sure whether this helps you any. -- Regards Roger Govier "mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message ... OK. I've got a list of dates expressed as text: January 2006 February 2006 ... ... ... December 2007 I've then got a cell containing the curret reporting period, expressed as text, February 2007 I'm looking for a formula which will look up the current reporting period within my list of dates and return the text date above it. Ultimately, I'll then use a VLookup to return the adjacent data, but I can manage that without too much help. I know this should be easy, but I've been messing about with this for an hour or so now, so... Any help appreciated. Tom. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Should be easy, but...
Lemme see if I understand. You have the "dates" (months actually as I
see them) in something like A2:A13. And you have, say, February 2007 in another cell, say F2. What would you like to get? January 2007? If so you do not really need the table in A2:A13. =TEXT(DATE(YEAR(DATEVALUE(1&" "&F2)),MONTH(DATEVALUE(1&" "&F2))-1,1),"Mmmm yyyy") Does this help? Kostis Vezerides On Mar 6, 3:06 pm, mr tom <mr-tom at mr-tom.co.uk.(donotspam) wrote: OK. I've got a list of dates expressed as text: January 2006 February 2006 ... ... ... December 2007 I've then got a cell containing the curret reporting period, expressed as text, February 2007 I'm looking for a formula which will look up the current reporting period within my list of dates and return the text date above it. Ultimately, I'll then use a VLookup to return the adjacent data, but I can manage that without too much help. I know this should be easy, but I've been messing about with this for an hour or so now, so... Any help appreciated. Tom. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Should be easy, but...
In essence, there will be a feed from a data warehouse dumping say 24 months
of data into a sheet. I want to select and chart data from the 12 months prior to the current one. Forget =TODAY() etc as I could run the report at any time. I'll just have the month and year entered into a cell (there's a master control for the reporting, so I'll use that. What I'm trying to do is, e.g. if the reporting month is Feb 07, I need to see the year to (but excluding feb), i.e. I need to pick all the entries from Feb 06 to Jan 07. Is this any clearer? Cheers, "vezerid" wrote: Lemme see if I understand. You have the "dates" (months actually as I see them) in something like A2:A13. And you have, say, February 2007 in another cell, say F2. What would you like to get? January 2007? If so you do not really need the table in A2:A13. =TEXT(DATE(YEAR(DATEVALUE(1&" "&F2)),MONTH(DATEVALUE(1&" "&F2))-1,1),"Mmmm yyyy") Does this help? Kostis Vezerides On Mar 6, 3:06 pm, mr tom <mr-tom at mr-tom.co.uk.(donotspam) wrote: OK. I've got a list of dates expressed as text: January 2006 February 2006 ... ... ... December 2007 I've then got a cell containing the curret reporting period, expressed as text, February 2007 I'm looking for a formula which will look up the current reporting period within my list of dates and return the text date above it. Ultimately, I'll then use a VLookup to return the adjacent data, but I can manage that without too much help. I know this should be easy, but I've been messing about with this for an hour or so now, so... Any help appreciated. Tom. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Should be easy, but...
Actually - just read that a second time. The formula will do the job as it
allows me to use one month to generate others as text values to include in a vlookup (although I suspect it'll fall over when it tries to move back from Jan 07 to Dec 06). Cheers, Tom. "mr tom" wrote: In essence, there will be a feed from a data warehouse dumping say 24 months of data into a sheet. I want to select and chart data from the 12 months prior to the current one. Forget =TODAY() etc as I could run the report at any time. I'll just have the month and year entered into a cell (there's a master control for the reporting, so I'll use that. What I'm trying to do is, e.g. if the reporting month is Feb 07, I need to see the year to (but excluding feb), i.e. I need to pick all the entries from Feb 06 to Jan 07. Is this any clearer? Cheers, "vezerid" wrote: Lemme see if I understand. You have the "dates" (months actually as I see them) in something like A2:A13. And you have, say, February 2007 in another cell, say F2. What would you like to get? January 2007? If so you do not really need the table in A2:A13. =TEXT(DATE(YEAR(DATEVALUE(1&" "&F2)),MONTH(DATEVALUE(1&" "&F2))-1,1),"Mmmm yyyy") Does this help? Kostis Vezerides On Mar 6, 3:06 pm, mr tom <mr-tom at mr-tom.co.uk.(donotspam) wrote: OK. I've got a list of dates expressed as text: January 2006 February 2006 ... ... ... December 2007 I've then got a cell containing the curret reporting period, expressed as text, February 2007 I'm looking for a formula which will look up the current reporting period within my list of dates and return the text date above it. Ultimately, I'll then use a VLookup to return the adjacent data, but I can manage that without too much help. I know this should be easy, but I've been messing about with this for an hour or so now, so... Any help appreciated. Tom. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Should be easy, but...
Hi Tom
If your Months January 2006, February 2006 etc are in cells B1:Z1 with your numeric data in rows 2 onward, then you could use the formula =SUM(INDEX(B2:Z2,MATCH($A$1,B$1:Z$1,0)):INDEX(B2:Z 2,12)) It would find the "month year" entered by you in A1, and starting from that column would sum 12 columns of data. -- Regards Roger Govier "mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message ... Actually - just read that a second time. The formula will do the job as it allows me to use one month to generate others as text values to include in a vlookup (although I suspect it'll fall over when it tries to move back from Jan 07 to Dec 06). Cheers, Tom. "mr tom" wrote: In essence, there will be a feed from a data warehouse dumping say 24 months of data into a sheet. I want to select and chart data from the 12 months prior to the current one. Forget =TODAY() etc as I could run the report at any time. I'll just have the month and year entered into a cell (there's a master control for the reporting, so I'll use that. What I'm trying to do is, e.g. if the reporting month is Feb 07, I need to see the year to (but excluding feb), i.e. I need to pick all the entries from Feb 06 to Jan 07. Is this any clearer? Cheers, "vezerid" wrote: Lemme see if I understand. You have the "dates" (months actually as I see them) in something like A2:A13. And you have, say, February 2007 in another cell, say F2. What would you like to get? January 2007? If so you do not really need the table in A2:A13. =TEXT(DATE(YEAR(DATEVALUE(1&" "&F2)),MONTH(DATEVALUE(1&" "&F2))-1,1),"Mmmm yyyy") Does this help? Kostis Vezerides On Mar 6, 3:06 pm, mr tom <mr-tom at mr-tom.co.uk.(donotspam) wrote: OK. I've got a list of dates expressed as text: January 2006 February 2006 ... ... ... December 2007 I've then got a cell containing the curret reporting period, expressed as text, February 2007 I'm looking for a formula which will look up the current reporting period within my list of dates and return the text date above it. Ultimately, I'll then use a VLookup to return the adjacent data, but I can manage that without too much help. I know this should be easy, but I've been messing about with this for an hour or so now, so... Any help appreciated. Tom. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Should be easy, but...
Thanks for answering, Roger.
What I need to do is chart (individually) the 12 months prior to the reporting one, so in essence, I need to find the current month (may not be TODAY()) and return results from one row above, two rows above etc. I've actually managed to manipulate the Biz Intelligence app to do this, but I'd still love to work out how Excel would do it. Essentially, rather than VLookup or HLookup, it's a diagonal lookup on different vectors. Am I making any sense? Cheers, Tom. "Roger Govier" wrote: Hi Tom If your Months January 2006, February 2006 etc are in cells B1:Z1 with your numeric data in rows 2 onward, then you could use the formula =SUM(INDEX(B2:Z2,MATCH($A$1,B$1:Z$1,0)):INDEX(B2:Z 2,12)) It would find the "month year" entered by you in A1, and starting from that column would sum 12 columns of data. -- Regards Roger Govier "mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message ... Actually - just read that a second time. The formula will do the job as it allows me to use one month to generate others as text values to include in a vlookup (although I suspect it'll fall over when it tries to move back from Jan 07 to Dec 06). Cheers, Tom. "mr tom" wrote: In essence, there will be a feed from a data warehouse dumping say 24 months of data into a sheet. I want to select and chart data from the 12 months prior to the current one. Forget =TODAY() etc as I could run the report at any time. I'll just have the month and year entered into a cell (there's a master control for the reporting, so I'll use that. What I'm trying to do is, e.g. if the reporting month is Feb 07, I need to see the year to (but excluding feb), i.e. I need to pick all the entries from Feb 06 to Jan 07. Is this any clearer? Cheers, "vezerid" wrote: Lemme see if I understand. You have the "dates" (months actually as I see them) in something like A2:A13. And you have, say, February 2007 in another cell, say F2. What would you like to get? January 2007? If so you do not really need the table in A2:A13. =TEXT(DATE(YEAR(DATEVALUE(1&" "&F2)),MONTH(DATEVALUE(1&" "&F2))-1,1),"Mmmm yyyy") Does this help? Kostis Vezerides On Mar 6, 3:06 pm, mr tom <mr-tom at mr-tom.co.uk.(donotspam) wrote: OK. I've got a list of dates expressed as text: January 2006 February 2006 ... ... ... December 2007 I've then got a cell containing the curret reporting period, expressed as text, February 2007 I'm looking for a formula which will look up the current reporting period within my list of dates and return the text date above it. Ultimately, I'll then use a VLookup to return the adjacent data, but I can manage that without too much help. I know this should be easy, but I've been messing about with this for an hour or so now, so... Any help appreciated. Tom. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Should be easy but....... | Excel Discussion (Misc queries) | |||
Probably and Easy one | Excel Discussion (Misc queries) | |||
Easy one... but not for me :S | Excel Worksheet Functions | |||
Is there an easy way to... | Excel Discussion (Misc queries) | |||
new user with easy question? not easy for me | New Users to Excel |