QUESTION: How to create a formulae to tag a cell with specific date and time in the slot as below, teh dates and time in the case be as follows 2/9/2009 11:32 2/13/2009 11:04 2/23/2009 18:21 3/14/2009 14:28 3/17/2009 10:21 3/17/2009 12:25 3/17/2009 17:19
the data needs to be tagged as following slots
06AM - 10 AM 10AM - 02PM 02PM - 06PM 06PM - 10PM 10 PM - 6 AM
ANSWER: As per my understanding of the situation, you want the corresponding cell to display a time slot. For example, if Cell A1 has the data "2/13/2009 11:04", then you want Cell B1 to display the time slot "10 AM - 02 PM"
Similarly, if the Cell A2 has the data "2/23/2009 18:21", then you want cell B2 to display the time slot"02 PM - 06 PM". If this is correct, then you may use the following formula to get the desired result.
=IF(AND(TEXT(A1,"HH:MM")>"06:00",(TEXT(A1,"HH:MM")<="10:00")),"06 AM - 10 AM",IF(AND(TEXT(A1,"HH:MM")>"10:00",(TEXT(A1,"HH:MM")<="14:00")),"10 AM - 02 PM",IF(AND(TEXT(A1,"HH:MM")>"14:00",(TEXT(A1,"HH:MM")<="18:00")),"02 PM - 06 PM",IF(AND(TEXT(A1,"HH:MM")>"18:00",(TEXT(A1,"HH:MM")<="22:00")),"06 PM - 10 PM",IF(AND(TEXT(A1,"HH:MM")>"22:00",(TEXT(A1,"HH:MM")<="06:00")),"10 PM - 06 AM")))))
The formula "TEXT" will display the time in Hour and Minutes. Then using "IF" you can you can get the desired results.
Please mail me the question in details in case your requirement is something different.
Thanks and Ranjan K Pandey
---------- FOLLOW-UP ----------
QUESTION: Thanks a lot for the revert, but some of timings are not getting converted to the specified slots 3/22/2009 0:17 10 PM - 6 AM but comes as false rest below cases come as false 3/23/2009 23:13 4/2/2009 23:01 4/9/2009 22:12 4/9/2009 22:58 4/13/2009 23:37 4/14/2009 23:29 5/1/2009 0:23 5/6/2009 23:10 5/7/2009 0:55 5/12/2009 0:00 5/16/2009 22:59 5/17/2009 1:25 5/17/2009 22:16 5/27/2009 23:31 5/28/2009 22:34 6/2/2009 0:18 6/2/2009 23:15 6/4/2009 23:24 6/5/2009 22:57 6/8/2009 22:42 6/8/2009 22:44 6/11/2009 0:20 6/13/2009 4:09 6/14/2009 2:06 6/15/2009 22:42 6/20/2009 1:44 6/22/2009 23:09 6/24/2009 0:07 6/27/2009 22:17 6/27/2009 23:10 6/28/2009 22:45 6/30/2009 0:51 6/30/2009 23:20 7/1/2009 0:32 7/2/2009 1:23 7/2/2009 22:24 7/2/2009 22:36 7/2/2009 23:17 7/4/2009 1:07 7/4/2009 2:21 7/4/2009 22:11 7/4/2009 22:24 7/4/2009 22:38 7/4/2009 22:40 7/6/2009 22:51 7/11/2009 0:03 7/14/2009 22:17 7/16/2009 23:09 7/16/2009 23:41 7/17/2009 0:35 7/23/2009 22:34 7/23/2009 23:55 7/28/2009 22:19 7/28/2009 23:07 7/28/2009 23:16 7/29/2009 1:44 7/30/2009 0:01 7/30/2009 23:00 7/30/2009 23:02 7/31/2009 0:58 8/1/2009 0:29 8/1/2009 4:34 8/6/2009 23:58 8/9/2009 3:24 8/10/2009 22:48 8/11/2009 3:36 8/11/2009 23:08 8/11/2009 23:47 8/12/2009 22:31 8/13/2009 22:22 8/13/2009 22:49 8/14/2009 23:38 8/18/2009 22:03 8/18/2009 23:10 8/20/2009 0:21 8/20/2009 1:49 8/20/2009 22:45 8/21/2009 0:00 8/21/2009 0:54 8/21/2009 22:53 8/21/2009 23:32 8/21/2009 23:41 8/26/2009 22:06 8/27/2009 0:51 8/28/2009 4:34 8/28/2009 22:10 8/28/2009 23:22 8/29/2009 23:40 8/29/2009 23:58 9/2/2009 0:15 9/2/2009 22:01 9/2/2009 22:13 9/5/2009 5:27 9/9/2009 23:57 9/10/2009 0:48 9/11/2009 23:58 9/12/2009 22:59 9/13/2009 22:15 9/14/2009 0:01 9/14/2009 22:50 9/15/2009 0:59 9/16/2009 22:40 9/16/2009 23:50 9/17/2009 22:05 9/19/2009 22:49 9/19/2009 22:51 9/21/2009 22:17 9/21/2009 22:26 9/22/2009 0:37 9/23/2009 22:12 9/23/2009 22:29 9/23/2009 22:32 9/23/2009 22:47 9/23/2009 23:03 9/24/2009 22:20 9/24/2009 22:31 9/25/2009 2:24 9/25/2009 22:31 9/25/2009 23:39 9/27/2009 22:26 9/28/2009 0:59 9/28/2009 23:05 9/28/2009 23:09 9/29/2009 4:08 9/29/2009 22:52 9/29/2009 23:05 9/29/2009 23:26 9/30/2009 22:21 9/30/2009 22:50 9/30/2009 22:50 9/30/2009 23:15 9/30/2009 23:56 10/1/2009 22:54 10/2/2009 22:45 10/4/2009 22:09 10/4/2009 22:51 10/5/2009 22:34 10/6/2009 1:53 10/6/2009 22:36 10/7/2009 23:50 10/8/2009 4:34 10/8/2009 23:51 10/9/2009 22:01 10/9/2009 22:25 10/9/2009 22:52 10/10/2009 22:08 10/10/2009 22:27 10/12/2009 23:40 10/13/2009 22:09 10/13/2009 22:38 10/13/2009 22:51 10/14/2009 23:33 10/14/2009 23:48 10/15/2009 0:04 10/17/2009 23:18 10/17/2009 23:47 10/19/2009 22:07 10/19/2009 23:07 10/20/2009 22:06 10/20/2009 22:23 10/21/2009 5:50 10/21/2009 22:19 10/21/2009 22:49 10/21/2009 23:06 10/22/2009 23:14 10/23/2009 0:12 10/23/2009 22:28 10/23/2009 22:37 10/24/2009 0:15 10/24/2009 1:21 10/24/2009 1:22 10/25/2009 22:15 10/25/2009 23:05 10/26/2009 0:02 10/26/2009 0:07 10/26/2009 5:19 10/26/2009 22:29 10/27/2009 23:11 10/27/2009 23:42 10/28/2009 3:31 10/28/2009 3:50 10/29/2009 0:02 10/29/2009 0:29 10/29/2009 1:00 10/29/2009 1:26 10/29/2009 22:19 10/29/2009 22:26 10/29/2009 22:44 10/29/2009 22:49 10/29/2009 23:29 10/30/2009 0:51 10/30/2009 22:58 10/30/2009 23:18 10/30/2009 23:36 10/30/2009 23:58 10/31/2009 0:26 10/31/2009 3:58 11/1/2009 22:47 11/1/2009 23:59 11/2/2009 22:02 11/2/2009 22:19 11/2/2009 22:53 11/3/2009 0:18 11/3/2009 2:16 11/3/2009 22:24 11/3/2009 22:34 11/3/2009 22:41 11/4/2009 0:43 11/4/2009 22:05 11/4/2009 22:59 11/4/2009 23:32 11/5/2009 22:31 11/5/2009 22:37 11/5/2009 23:10 11/5/2009 23:16 11/5/2009 23:17 11/5/2009 23:39 11/6/2009 1:16 11/6/2009 23:26 11/7/2009 0:25 11/7/2009 0:44 11/7/2009 0:50 11/7/2009 22:03 11/7/2009 22:13 11/7/2009 22:14 11/8/2009 23:14 11/8/2009 23:29 11/9/2009 22:15 11/9/2009 22:37 11/9/2009 23:05 11/9/2009 23:46 11/10/2009 23:01 11/12/2009 22:02 11/12/2009 22:27 11/12/2009 22:45 11/12/2009 23:36 11/12/2009 23:36 11/13/2009 0:30 11/13/2009 22:30 11/14/2009 0:39 11/14/2009 23:07 11/14/2009 23:35 11/15/2009 2:06 11/15/2009 22:03 11/15/2009 22:10 11/15/2009 22:39 11/15/2009 22:51 11/15/2009 23:10 11/15/2009 23:35 11/16/2009 0:12 11/16/2009 22:27 11/16/2009 23:12 11/17/2009 1:36 11/17/2009 22:33 11/17/2009 23:31 11/17/2009 23:35 11/17/2009 23:43 11/17/2009 23:53 11/18/2009 22:40 11/19/2009 0:39 11/21/2009 1:22 11/21/2009 22:40 11/22/2009 0:44 11/23/2009 0:30 11/23/2009 22:05 11/23/2009 23:46 11/24/2009 0:09 11/24/2009 4:49 11/24/2009 5:10 11/24/2009 5:39 11/24/2009 22:42 11/24/2009 22:45 11/24/2009 23:16 11/24/2009 23:20 11/25/2009 23:05 11/25/2009 23:38 11/26/2009 0:51 11/26/2009 2:51 11/26/2009 22:11 11/29/2009 0:58 11/29/2009 22:12 12/1/2009 0:00 12/1/2009 1:03 12/1/2009 22:20 12/1/2009 22:59 12/2/2009 2:34 12/2/2009 22:26 12/2/2009 22:55 12/2/2009 23:15 12/2/2009 23:35 12/2/2009 23:49 12/3/2009 22:20 12/3/2009 22:54 12/3/2009 23:08 12/3/2009 23:21 12/4/2009 0:07 12/5/2009 22:22 12/6/2009 22:54 12/7/2009 23:59 12/8/2009 23:40 12/8/2009 23:42 12/9/2009 22:33 12/9/2009 23:13 12/11/2009 23:01 12/11/2009 23:49 12/12/2009 1:40 12/13/2009 0:44 12/13/2009 1:07 12/14/2009 22:19 12/14/2009 23:14 12/16/2009 0:30 12/16/2009 1:19 12/18/2009 22:16 12/18/2009 23:23 12/19/2009 4:14 12/19/2009 5:50 12/20/2009 0:31 12/20/2009 0:39 12/20/2009 23:57 12/21/2009 23:03 12/22/2009 0:55 12/22/2009 3:46 12/22/2009 4:43 12/22/2009 5:39 12/22/2009 23:52 12/23/2009 1:43 12/23/2009 22:01 12/23/2009 22:32 12/24/2009 0:30 12/26/2009 23:35 12/28/2009 2:01 12/29/2009 22:03 12/29/2009 22:12 12/29/2009 22:16 12/29/2009 22:21 12/29/2009 22:53 12/29/2009 23:23 12/29/2009 23:48 1/1/2010 23:50 1/2/2010 1:16 1/2/2010 22:38 1/5/2010 23:31 1/5/2010 23:34 1/6/2010 0:44 1/6/2010 23:26 1/8/2010 0:23 1/8/2010 22:03 1/8/2010 22:07 1/8/2010 22:15 1/8/2010 23:36 1/9/2010 1:47 1/9/2010 23:57 1/10/2010 23:14 1/10/2010 23:20 1/11/2010 22:44 1/12/2010 2:00 1/13/2010 23:00 1/14/2010 23:52 1/14/2010 23:57 1/17/2010 22:16 1/17/2010 22:56 1/18/2010 23:05 1/19/2010 23:30 1/21/2010 0:50 1/21/2010 22:17 1/21/2010 23:55 1/22/2010 23:23 1/23/2010 0:25 1/23/2010 22:14 1/24/2010 22:32 1/26/2010 22:06 1/26/2010 23:13 1/26/2010 23:14 1/27/2010 22:48 1/30/2010 22:45 2/1/2010 0:02 2/1/2010 22:08 2/1/2010 22:23 2/1/2010 22:24 2/2/2010 23:09
Hello Anand,
My apologies for not testing the formula in different timeslots. The formula I gave had an Error. A time cannot be both less than 6:00 and greater than 22:00
Please note that the formula gives an Error only for the timeslot 10 PM - 6 AM.
Use the following formula:
=IF(AND(TEXT(A1,"HH:MM")>"06:00",(TEXT(A1,"HH:MM")<="10:00")),"06 AM - 10 AM",IF(AND(TEXT(A1,"HH:MM")>"10:00",(TEXT(A1,"HH:MM")<="14:00")),"10 AM - 02 PM",IF(AND(TEXT(A1,"HH:MM")>"14:00",(TEXT(A1,"HH:MM")<="18:00")),"02 PM - 06 PM",IF(AND(TEXT(A1,"HH:MM")>"18:00",(TEXT(A1,"HH:MM")<="22:00")),"06 PM - 10 PM","10 PM - 06 AM"))))
I have now changed the formula to display "10 PM - 06 AM" in case the time does not fit into one of the 4 timeslots. i.e.
06AM - 10 AM 10AM - 02PM 02PM - 06PM 06PM - 10PM
Sorry for any inconvenience.
Thanks and Ranjan K Pandey
Advertisement