Lookup help
Hello
I have a grid with hourly data. For each day of the month. Hours ending 1-24 in cells C2:Z2 and date in B2:B33. I am trying to convert the tabular data to columnar data. In B41:B785 I have the date/time dd/mm/yyyy hh:mm. I can use INDEX(My_Range,DAY($B41),HOUR($B41)) for hours 1-23 but it doesnt work in 24. I could change my table to 0-23 and it would work but I would rather not do that. Any ideas? Thanks! |
Lookup help
On second though changing 0-23 wont work :-)
"Sandy" wrote: Hello I have a grid with hourly data. For each day of the month. Hours ending 1-24 in cells C2:Z2 and date in B2:B33. I am trying to convert the tabular data to columnar data. In B41:B785 I have the date/time dd/mm/yyyy hh:mm. I can use INDEX(My_Range,DAY($B41),HOUR($B41)) for hours 1-23 but it doesnt work in 24. I could change my table to 0-23 and it would work but I would rather not do that. Any ideas? Thanks! |
Lookup help
On second though changing 0-23 wont work :-)
It should: =INDEX(My_Range,DAY($B41),MATCH(HOUR($B41),$C$2:$Z $2,0)) Where C2:Z2 = 0 to 23 -- Biff Microsoft Excel MVP "Sandy" wrote in message ... On second though changing 0-23 wont work :-) "Sandy" wrote: Hello I have a grid with hourly data. For each day of the month. Hours ending 1-24 in cells C2:Z2 and date in B2:B33. I am trying to convert the tabular data to columnar data. In B41:B785 I have the date/time dd/mm/yyyy hh:mm. I can use INDEX(My_Range,DAY($B41),HOUR($B41)) for hours 1-23 but it doesnt work in 24. I could change my table to 0-23 and it would work but I would rather not do that. Any ideas? Thanks! |
Lookup help
Thanks!
"T. Valko" wrote: On second though changing 0-23 wont work :-) It should: =INDEX(My_Range,DAY($B41),MATCH(HOUR($B41),$C$2:$Z $2,0)) Where C2:Z2 = 0 to 23 -- Biff Microsoft Excel MVP "Sandy" wrote in message ... On second though changing 0-23 wont work :-) "Sandy" wrote: Hello I have a grid with hourly data. For each day of the month. Hours ending 1-24 in cells C2:Z2 and date in B2:B33. I am trying to convert the tabular data to columnar data. In B41:B785 I have the date/time dd/mm/yyyy hh:mm. I can use INDEX(My_Range,DAY($B41),HOUR($B41)) for hours 1-23 but it doesnt work in 24. I could change my table to 0-23 and it would work but I would rather not do that. Any ideas? Thanks! |
Lookup help
Hey Biff
One little problem with the hour 24 of the last day of the month, it shows as day 1. Is there a work around?? Thanks for your help "T. Valko" wrote: On second though changing 0-23 wont work :-) It should: =INDEX(My_Range,DAY($B41),MATCH(HOUR($B41),$C$2:$Z $2,0)) Where C2:Z2 = 0 to 23 -- Biff Microsoft Excel MVP "Sandy" wrote in message ... On second though changing 0-23 wont work :-) "Sandy" wrote: Hello I have a grid with hourly data. For each day of the month. Hours ending 1-24 in cells C2:Z2 and date in B2:B33. I am trying to convert the tabular data to columnar data. In B41:B785 I have the date/time dd/mm/yyyy hh:mm. I can use INDEX(My_Range,DAY($B41),HOUR($B41)) for hours 1-23 but it doesnt work in 24. I could change my table to 0-23 and it would work but I would rather not do that. Any ideas? Thanks! |
Lookup help
Hmmm...
Well, I'd need to be able see what you're trying to do to figure it out. If you can/want to send me a small sample file that demonstrates exactly what you're doing I should be able to figure it out. If you want to do that I'm at: xl can help at comcast period net Remove "can" and change the obvious. Or, you can upload a small sample file to a free file host then post a link to that file. Here are a couple of free file hosts: http://translate.google.com/translat...l%3Den%26lr%3D http://www.freefilehosting.net The first link is one I use frequently. It's a French site translated to English. The uploaded file is removed after a few weeks and the link will "break" at that time. -- Biff Microsoft Excel MVP "Sandy" wrote in message ... Hey Biff One little problem with the hour 24 of the last day of the month, it shows as day 1. Is there a work around?? Thanks for your help "T. Valko" wrote: On second though changing 0-23 wont work :-) It should: =INDEX(My_Range,DAY($B41),MATCH(HOUR($B41),$C$2:$Z $2,0)) Where C2:Z2 = 0 to 23 -- Biff Microsoft Excel MVP "Sandy" wrote in message ... On second though changing 0-23 wont work :-) "Sandy" wrote: Hello I have a grid with hourly data. For each day of the month. Hours ending 1-24 in cells C2:Z2 and date in B2:B33. I am trying to convert the tabular data to columnar data. In B41:B785 I have the date/time dd/mm/yyyy hh:mm. I can use INDEX(My_Range,DAY($B41),HOUR($B41)) for hours 1-23 but it doesnt work in 24. I could change my table to 0-23 and it would work but I would rather not do that. Any ideas? Thanks! |
All times are GMT +1. The time now is 06:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com