software

I have a list of employees whose start and end time are contained in the
same cell, in the same column. The format looks like this: 11:00-21:30,
but with varying start and end times. I need to keep that format for
internal purposes. I need a formula that will count the number of
employees starting before a specified time, say 11:00 for example.--
monger
------------------------------------------------------------------------
monger's Profile: www.excelforum.com/member.php...oamp;userid=17911
View this thread: www.excelforum.com/showthread...hreadid=511258I would create a new column and use a formula like:

=--LEFT(A2,SEARCH(quot;-quot;,A2)-1))
to extract the time.
(format as time)Then I'd use =countif() against that column.
=countif(b2:b8,quot;lt;=quot;amp;time(11,0,0))

But you could do it in one formula:

=SUMPRODUCT(--(TIMEVALUE(LEFT(A2:A8,SEARCH(quot;-quot;,A2:A8)-1))lt;=TIME(11,0,0)))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

monger wrote:
gt;
gt; I have a list of employees whose start and end time are contained in the
gt; same cell, in the same column. The format looks like this: 11:00-21:30,
gt; but with varying start and end times. I need to keep that format for
gt; internal purposes. I need a formula that will count the number of
gt; employees starting before a specified time, say 11:00 for example.
gt;
gt; --
gt; monger
gt; ------------------------------------------------------------------------
gt; monger's Profile: www.excelforum.com/member.php...oamp;userid=17911
gt; View this thread: www.excelforum.com/showthread...hreadid=511258

--

Dave Peterson


Perfect! Thank you--
monger
------------------------------------------------------------------------
monger's Profile: www.excelforum.com/member.php...oamp;userid=17911
View this thread: www.excelforum.com/showthread...hreadid=511258