Nested IF functions using time values [Solved/Closed]

Report
-
 Harshada Chaudhari -
Hello,

can someone tell why this wont work. The time value functions work separately on their own, and I followed the excel help for nested if functions.

=IF((B2>=TIMEVALUE("6:00:00"))*(B2<=TIMEVALUE("10:30:00")),"Morning",IF(B2>=TIMEVALUE("10:31:00"))*(B2<=TIMEVALUE("15:00:00")),"MidDay",IF(B2>=TIMEVALUE("15:01:00"))*(B2<=TIMEVALUE("18:30:00")),"Afternoon","Evening" )

2 replies

Posts
1023
Registration date
Saturday May 23, 2009
Status
Contributor
Last seen
October 20, 2010
244
Dude, had a mess around in excel 2007 and this works to what you are intending. you were nearly there in what you had. just needed a few ANDs in the right places.

=IF(AND(B2>=TIMEVALUE("6:00:00"))*(B2<=TIMEVALUE("10:30:00")),"Morning",IF(AND(B2>=TIMEVALUE("10:31:00"))*(B2<=TIMEVALUE("15:00:00")),"MidDay",IF(AND(B2>=TIMEVALUE("15:01:00"))*(B2<=TIMEVALUE("18:30:00")),"Afternoon","Evening")))

hope this helps.
16
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Hi guys... can some one please help me out ... I m new to excel...
i have set of Time and i wanted to categorise them into each one hour...
for Example -

Time
X = 1:13 AM
X = 2:15 AM
X = 6:19 AM
X = 7:15 AM

if my X = 1:13 AM time is falling between range of 1:00 AM to 1:59 AM then the value should return the category as "1 AM to 1:59 AM" , if my X = 2:15 AM time is falling between range of 2:00 AM to 2:59 AM then the value should return the category as "2 AM to 2:59 AM" .........

Categories are below -

1 AM to 1:59 AM
2 AM to 2:59 AM
3 AM to 3:59 AM
4 AM to 4:59 AM
5 AM to 5:59 AM
6 AM to 6:59 AM
7 AM to 7:59 AM
8 AM to 8:59 AM
9 AM to 9:59 AM
10 AM to 10:59 AM
11 AM to 11:59 AM
12 AM to 12:59 AM

please help me to formulate the function

Thanks for you are formula method
This formula help me a lot