Number Of Occurrence

[kelvin_9]

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]sunday[/TD]
[TD]monday[/TD]
[TD]tuesday[/TD]
[TD]wednesday[/TD]
[TD]thursday[/TD]
[TD]friday[/TD]
[TD]saturday[/TD]
[/TR]
[TR]
[TD]peter[/TD]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl66, width: 82"]09:30 - 15:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl66, width: 82"]15:00 - 22:45[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl66, width: 82"]09:30 - 15:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]amy[/TD]
[TD][/TD]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl66, width: 82"]09:30 - 19:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl66, width: 82"]13:15 - 22:45[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]09:30 - 19:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]before 13:00[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]after 13:00[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

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