Counting by combined start and end time

[monger]

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.

[Dave Peterson]

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

[monger]

Perfect! Thank you