#1
|
|||
|
|||
If And conditions for Time
Column A = time formatted in military time : 9:50
Column B= “Include” if time is between 7:59 (i.e., 7:59AM and 17:59 (i.e., 5:59 PM). If outside those parameters, “” in Column B. This is my formula: =IF(AND(A511>TIMEVALUE("7:59:00"),A511<TIMEVALUE(" 17:59:00")),"Include","") The return value in Column B is “” when the time is within the parameters. The return value should be “Include”. Need help with the formula |
#2
|
|||
|
|||
Code:
=IF(AND(A511>=TIME(8,0,0),A511<TIME(18,0,0)),"Include","") |
#3
|
||||
|
||||
Could you please post a small sample sheet with some data ( go advanced - Manage attachments)
Military time is like 0950 not 9:50
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#4
|
|||
|
|||
Your help
The formula did not work and I think it is due to the way I have time formatted. I used the drop down menu in Excel and selected the format 13:30 which I thought was military time but times in my spread sheet less than 12 pm did not have 0 (e.g., 0930). The time in the cell was 9:30. I tried other formats and none work. Any thoughts?
|
#5
|
|||
|
|||
Probably your time is really a datetime formatted to show time part only.
Try to format cell with time in table as datetime, like "yyyy.mm.dd hh:nn:ss", and then look at data. When the date part is 01.01.1900, then the field is time only, otherwise it is datetime. I prefer to format dates with Custom format - in your case it will be "hh:nn". |
#6
|
||||
|
||||
Please post a sheet as requested in post #3.
There are various possibilities where time and dates are concerned, that we could keep guessing a long while. A sheet would help find the answer much faster
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Sum with conditions | xling | Excel | 2 | 08-15-2017 12:16 AM |
Sumif with Conditions | Haha88 | Excel | 2 | 12-21-2016 12:08 AM |
6 conditions formula | paconovellino | Excel | 2 | 03-05-2012 06:45 AM |
3 Conditions | Laurie B. | Excel | 4 | 08-15-2011 10:27 PM |
how to set an equation with three conditions? | Jamal NUMAN | Word | 0 | 07-09-2011 06:05 AM |