another Excel question for you brilliant OOTiots!
 

I have a column A, formatted for time, like:

13:45
9:38

etc.

Next to it I would like each cell in column B to take its corresponding cell from A, and return a number 1 through 4, depending on which 6 hour period the time from column A falls in.

so,

00:00 to 5:59 --> 1
6:00 to 11:59 --> 2
12:00 to 17:59 --> 3
18:00 to 23:59 --> 4

Any idea how to code column B?

thanks guys,

Rhone.

Re: another Excel question for you brilliant OOTiots!
 

=IF(HOUR(A1)<6,1,IF(HOUR(A1)<12,2,IF(HOUR(A1 )<18,3,4)))

Re: another Excel question for you brilliant OOTiots!
 

You beat me to it!

Re: another Excel question for you brilliant OOTiots!
 

I don't know how to begin to code that, but you could sort them out pretty quickly into groups using the custom autofilter button. Then copy and paste a 1-4 in column b. This would yield the same result. How many workbooks do you need to do this to?

Thanks! (n/m)
 

THANKS, EVERYONE!

Re: another Excel question for you brilliant OOTiots!
 

a less cludgy solution:

=INT(HOUR(A1)/6)+1

you'll probably have to manually format the column as an integer

Re: another Excel question for you brilliant OOTiots!
 

This is fine, the IF statement code that's written. Just keep in mind that if your conditions were just a little more complicated, you should abandon an IF statement and use VLOOKUP. There's a great Excel forum you can use for complicated advice, PM me for that or for other Excel help.

Rob

Re: another Excel question for you brilliant OOTiots!
 

i don't get why you'd use a bunch of nested IF's when it's obviously just a math problem.