Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Checking time and inserting a value in a different cell
I have Column H that has been formatted as time (i.e. 1:30 PM).
If the value in column H is Less than 07:30 and Greater than 15:30, I would like the number 41079 populated in the appropriate cell in column J. If the test is not False, I would like the value 41078 populated. I have inserted the following formula in H6 €˜=IF(H615:30 and <7:30,41078,41079), however when I depress the enter key J6 displays #NAME. Can you help? Thanks Pank |
#2
|
|||
|
|||
Hi Pank,
I think the easiest thing to do would be to put your low (07:30) and high (15:30) times in a couple of cells somewhere and then refer to the cells, rather than trying to hard-code the time value within the formula. I have placed 07:30 in C1 and 15:30 in D1 and my formula in column J (row 1) is as follows: =IF(AND(H1=$C$1,H1<=$D$1),41078,41079) I've just noticed that you have specified a time both less than 07:30 and greater than 15:30. This is not logically possible. I presume you are talking about <07:30 on a different day? Hope this helps, Mark "Pank" wrote: I have Column H that has been formatted as time (i.e. 1:30 PM). If the value in column H is Less than 07:30 and Greater than 15:30, I would like the number 41079 populated in the appropriate cell in column J. If the test is not False, I would like the value 41078 populated. I have inserted the following formula in H6 €˜=IF(H615:30 and <7:30,41078,41079), however when I depress the enter key J6 displays #NAME. Can you help? Thanks Pank |
#3
|
|||
|
|||
Hi
Your logic is perhaps incorrect, unless you are dealing with differing days. If you are checking whether the time is between 7:30 and 15:30 on the same day then try the following. =IF(AND(H6=TIME(7,30,0),H6<=TIME(15,30,0)),41078, 41079) Your time in H6 would need to be entered in 24 hr format though, 13:30 and not 1:30 which would give the false result. Regards Roger Govier Pank wrote: I have Column H that has been formatted as time (i.e. 1:30 PM). If the value in column H is Less than 07:30 and Greater than 15:30, I would like the number 41079 populated in the appropriate cell in column J. If the test is not False, I would like the value 41078 populated. I have inserted the following formula in H6 €˜=IF(H615:30 and <7:30,41078,41079), however when I depress the enter key J6 displays #NAME. Can you help? Thanks Pank |
#4
|
|||
|
|||
Mark, Roger,
Thanks for the prompt response, worked a treat. Regards Pank "Roger Govier" wrote: Hi Your logic is perhaps incorrect, unless you are dealing with differing days. If you are checking whether the time is between 7:30 and 15:30 on the same day then try the following. =IF(AND(H6=TIME(7,30,0),H6<=TIME(15,30,0)),41078, 41079) Your time in H6 would need to be entered in 24 hr format though, 13:30 and not 1:30 which would give the false result. Regards Roger Govier Pank wrote: I have Column H that has been formatted as time (i.e. 1:30 PM). If the value in column H is Less than 07:30 and Greater than 15:30, I would like the number 41079 populated in the appropriate cell in column J. If the test is not False, I would like the value 41078 populated. I have inserted the following formula in H6 €˜=IF(H615:30 and <7:30,41078,41079), however when I depress the enter key J6 displays #NAME. Can you help? Thanks Pank |
#5
|
|||
|
|||
Your formula needs to look like this: =IF(AND(H6TIME(15,30,0),H6<TIME(7,30,0)),41078,41 079) an entry of 13:30 (1:30PM) in H6 returns 41079 HTH Bruce -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=467072 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to insert an empty row at the same time in more than one sheet? | Excel Discussion (Misc queries) | |||
Inserting time in cell to the right | Excel Worksheet Functions | |||
More problems displaying a negative time | Excel Discussion (Misc queries) | |||
How to exclude weekends/holidays from plotted time series | Charts and Charting in Excel | |||
Time & Date Formatting | Excel Worksheet Functions |