Countifs Time Formula

Bound4Glory

New Member
Joined
Jun 21, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
EE ID​
NAME​
Clock In​
Clock Out​
TOTAL​
Start Time​
End Time​
Employees​
481ALLEN18:0021:0003:000:000:590
15407ANTHONY20:002:001:001:590
12553STEVE2:002:590
14276MARK3:003:590
13851BRANDY16:0022:0006:004:004:590
13967BARRY15:0021:0006:005:005:590
980SMITH9:0015:0006:006:006:590
7:007:590
8:008:591
9:009:591
10:0010:591
11:0011:591
12:0012:591
13:0013:591
14:0014:591
15:0015:592
16:0016:592
17:0017:592
18:0018:592
19:0019:592
20:0020:591
21:0021:590
22:0022:590
23:0023:590

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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.
 
Upvote 0
Solution
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 IDEMPLOYEE NAMEClock InClock OutTOTAL
481ALLEN,02/24 18:0002/24 21:0003:00
15407ANTHONY02/24 20:0002/25 02:0006:00
12553STEVE
14276MARK
13851BRANDY02/24 16:0002/24 22:0006:00
13967BARRY02/24 15:0002/24 21:0006:00
980SMITH02/24 09:0002/24 15:0006:00
 
Upvote 0
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 IDEMPLOYEE NAMEClock InClock OutTOTAL
481ALLEN,02/24 18:0002/24 21:0003:00
15407ANTHONY02/24 20:0002/25 02:0006:00
12553STEVE
14276MARK
13851BRANDY02/24 16:0002/24 22:0006:00
13967BARRY02/24 15:0002/24 21:0006:00
980SMITH02/24 09:0002/24 15:0006:00
why are your time and dates formatted as text? Or do you have the time left aligned?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top