Number Of Occurrence

[kelvin_9]

	sunday	monday	tuesday	wednesday	thursday	friday	saturday
peter	09:30 - 15:00 <tbody> </tbody>		15:00 - 22:45 <tbody> </tbody>			09:30 - 15:00 <tbody> </tbody>
amy		09:30 - 19:00 <tbody> </tbody>		13:15 - 22:45 <tbody> </tbody>		09:30 - 19:00
before 13:00	1	1	0	0	0	2	0
after 13:00	0	0	1	1	0	0	0

<tbody>
</tbody>

hello all,
with this table of my staff schedule, i would like to count the occurrence of daily before 13:00 / after 13:00, how can i do this like "left and countif"?
format cell need to be hh:mm

thanks all your great answer

 

[kelvin_9]

any idea?

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G
1	sunday	monday	tuesday	wednesday	thursday	friday	saturday
2	peter	09:30 - 15:00		15:00 - 22:45			09:30 - 15:00
3
4	amy		09:30 - 19:00		13:15 - 22:45		09:30 - 19:00
5
6	before 13:00	1	1	0	0	0	2
7	after 13:00	0	0	1	1	0	0
Sheet1

Cell Formulas
Range	Formula
B6	{=SUM(IF(B2:B4="",0,IF(0+LEFT(B2:B4,5)13,0,0),1,0)))}
B7	{=SUM(IF(B2:B4="",0,IF(0+LEFT(B2:B4,5)>=TIME(13,0,0),1,0)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[FormR]

Hi, another option you can try:

Excel 2013/2016
	A	B	C	D	E	F	G
1	sunday	monday	tuesday	wednesday	thursday	friday	saturday
2	peter	13:00 - 15:00		15:00 - 22:45			09:30 - 15:00
3
4	amy		09:30 - 19:00		13:15 - 22:45		09:30 - 19:00
5
6	before 13:00	1	1	0	0	0	2
7	after 13:00	0	0	1	1	0	0
Sheet1

Cell Formulas
Range	Formula
B6	=COUNTIFS(B2:B4,"<=13:00 - 99:99")
B7	=COUNTIFS(B2:B4,">13:00 - 99:99")

 

[kelvin_9]

both of you are life saver
thanks for the great solution

i have another formula/macros issue