#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Re: another Excel question for you brilliant OOTiots!
=IF(HOUR(A1)<6,1,IF(HOUR(A1)<12,2,IF(HOUR(A1 )<18,3,4)))
|
#3
|
|||
|
|||
Re: another Excel question for you brilliant OOTiots!
You beat me to it!
|
#4
|
|||
|
|||
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?
|
#5
|
|||
|
|||
Thanks! (n/m)
THANKS, EVERYONE!
|
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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.
|
|
|