Menu
Ask a question Report

Nested IF functions using time values [Solved/Closed]

SharkMerry - Aug 31, 2009 at 03:35 PM - Latest reply:  Harshada Chaudhari
- Dec 21, 2016 at 05:20 AM
Office Software Ask a question Report
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" )
See more 

5 replies

Best answer
Report Respond to sharpman
sharpman 1023 Posts Saturday May 23, 2009Registration dateContributorStatus October 20, 2010 Last seen - Sep 1, 2009 at 04:54 PM
14
Thank you
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.

Thank you, sharpman 14

Something to say? Add comment

CCM has helped 1786 users this month

Report
navi - Jun 2, 2016 at 04:05 PM
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
Report
Harshada Chaudhari - Dec 21, 2016 at 05:20 AM
Thanks for you are formula method
This formula help me a lot
Report Respond to kenjo
kenjo - May 4, 2011 at 01:40 PM
0
Thank you
thanks ^_^
Report
SW - Oct 18, 2016 at 04:11 PM
Genius! Thanks!
Ask a question
Featured