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: http://www.excelforum.com/member.php...o&userid=17911
View this thread: http://www.excelforum.com/showthread...hreadid=511258

I would create a new column and use a formula like:

=--LEFT(A2,SEARCH("-",A2)-1))
to extract the time.
(format as time)


Then I'd use =countif() against that column.
=countif(b2:b8,"<="&time(11,0,0))

But you could do it in one formula:

=SUMPRODUCT(--(TIMEVALUE(LEFT(A2:A8,SEARCH("-",A2:A8)-1))<=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:

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: http://www.excelforum.com/member.php...o&userid=17911
View this thread: http://www.excelforum.com/showthread...hreadid=511258

--

Dave Peterson

Perfect! Thank you


--
monger
------------------------------------------------------------------------
monger's Profile: http://www.excelforum.com/member.php...o&userid=17911
View this thread: http://www.excelforum.com/showthread...hreadid=511258