I have a list of start and end times. I need to weed out any hours that fall
between 8:00a and 17:00 (5:00p). I've tried several different formulas
without success so I'm hoping somebody can give me a formula to weed out
those hours. Here is a sample of the data I have:

a 11:04 14:26
b 17:16 18:52
c 16:11 17:00
d 21:37 21:39
e 7:12 7:16

In this data set, a and c are the lines that I want to give a value of 1
meaning it falls with regular work hours between 8:00a & 5:00p. Lines b, d
and e I want to give a value of 2 meaning it falls outside regular work hours.

Thanks!

Perhaps something like this:
=IF(OR(A2<TIMEVALUE("8:00"),B2TIMEVALUE("17:00"), 2,1)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"CJ" wrote:

I have a list of start and end times. I need to weed out any hours that fall
between 8:00a and 17:00 (5:00p). I've tried several different formulas
without success so I'm hoping somebody can give me a formula to weed out
those hours. Here is a sample of the data I have:

a 11:04 14:26
b 17:16 18:52
c 16:11 17:00
d 21:37 21:39
e 7:12 7:16

In this data set, a and c are the lines that I want to give a value of 1
meaning it falls with regular work hours between 8:00a & 5:00p. Lines b, d
and e I want to give a value of 2 meaning it falls outside regular work hours.

Thanks!

Try:

=IF(AND(A1=TIME(8,0,0),B1<=TIME(17,0,0)),1,2)

copied down.


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116247