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!

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!

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!

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!

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!

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!