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

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

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

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

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