Checking time and inserting a value in a different cell

[Guest]

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(H6>15:30 and
<7:30,41078,41079)’, however when I depress the enter key J6 displays #NAME.

Can you help?

Thanks

Pank

 

[Guest]

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

 

[Roger Govier]

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

 

[Guest]

Mark, Roger,

Thanks for the prompt response, worked a treat.

Regards

Pank

 

[swatsp0p]

Your formula needs to look like this:

=IF(AND(H6>TIME(15,30,0),H6<TIME(7,30,0)),41078,41079)

an entry of 13:30 (1:30PM) in H6 returns 41079

HTH

Bruce