Countifs Time Formula

[Bound4Glory]

EE ID​	NAME​	Clock In​	Clock Out​	TOTAL​		Start Time​	End Time​	Employees​
481	ALLEN	18:00	21:00	03:00		0:00	0:59	0
15407	ANTHONY	20:00	2:00			1:00	1:59	0
12553	STEVE					2:00	2:59	0
14276	MARK					3:00	3:59	0
13851	BRANDY	16:00	22:00	06:00		4:00	4:59	0
13967	BARRY	15:00	21:00	06:00		5:00	5:59	0
980	SMITH	9:00	15:00	06:00		6:00	6:59	0
						7:00	7:59	0
						8:00	8:59	1
						9:00	9:59	1
						10:00	10:59	1
						11:00	11:59	1
						12:00	12:59	1
						13:00	13:59	1
						14:00	14:59	1
						15:00	15:59	2
						16:00	16:59	2
						17:00	17:59	2
						18:00	18:59	2
						19:00	19:59	2
						20:00	20:59	1
						21:00	21:59	0
						22:00	22:59	0
						23:00	23:59	0

I'm using a Countifs formula to get the number of employees during each hour, however, if an employee is clocked in overnight, it won't work properly. Any suggestions on how I could solve this?

=COUNTIFS($C$3:$C$70,"<="&G23,$D$3:$D$70,">"&H23)

Thank you

 

[awoohaw]

when you enter time you are entering time from midnight on 12/31/1899. 2 am is less than 6 pm, but excel does not know it is the next day.
one way to fix it is to do an if statement adding 1 to the times where end time is less than start time. But this means that you can't have someone more than 1 overnight or you'll have the same problem again. i'll try to figure a forumula to post in a minute.
but one way to avoid the issue is to include the date when entering the time.

 

[Bound4Glory]

Thank you for your response, I am able to add the date into columns C and D. Now I'm just not sure how to adjust my countif statement.

EE ID

EMPLOYEE NAME

Clock In

Clock Out

TOTAL

481	ALLEN,	02/24 18:00	02/24 21:00	03:00
15407	ANTHONY	02/24 20:00	02/25 02:00	06:00
12553	STEVE
14276	MARK
13851	BRANDY	02/24 16:00	02/24 22:00	06:00
13967	BARRY	02/24 15:00	02/24 21:00	06:00
980	SMITH	02/24 09:00	02/24 15:00	06:00

 

[awoohaw]

okay, then that makes it easier..

 

[awoohaw]

Thank you for your response, I am able to add the date into columns C and D. Now I'm just not sure how to adjust my countif statement.

EE ID

EMPLOYEE NAME

Clock In

Clock Out

TOTAL

481	ALLEN,	02/24 18:00	02/24 21:00	03:00
15407	ANTHONY	02/24 20:00	02/25 02:00	06:00
12553	STEVE
14276	MARK
13851	BRANDY	02/24 16:00	02/24 22:00	06:00
13967	BARRY	02/24 15:00	02/24 21:00	06:00
980	SMITH	02/24 09:00	02/24 15:00	06:00

why are your time and dates formatted as text? Or do you have the time left aligned?

 

[Bound4Glory]

Total is formatted as Time. Clock In and Clock Out is formatted as custom "MM/DD HH:MM"

 

[Bound4Glory]

I was able to solve this by using a formula to add the date into the other table as well. The same formula I was using works now.

 

[awoohaw]

I'm pleased you found a solution!