Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

How to count number of employees days on work between 2 dates (countif etc)

Copper Contributor

Hi, I'm trying to figure out how to make excel check 2 dates on each row and see if if that person is active on work. 

 

For example.

Employee 1 works 16 feb to 3 march

Employee 2 works on 17 feb to 4 march

 

I wanna count each day and show how many work on each day on shift.

In this simple example on 16th I have 1 employee working, and on 17th I have 2 etc. 

 

Any tips on moving on? I have been playing around with countif but no success yet :)

I basically wanna fil out the Persons on shift for each day from the "calendar" above that's 2 dates in each column. 

 

Swe_Mack_0-1707678226623.png

 

8 Replies

@Swe_Mack 

Let's say the start dates are in B3:B10 and the finish dates in C3:C10.

 

The lit of dates below starts in A15.

In B15:

=COUNTIFS($B$3:$B$10, "<="&A15, $C$3:$C$10, ">=&A15)

Fill down.

Thanks for helping out! I made a simple excel and put the boxes in. But I can't get it to work..

https://1drv.ms/x/s!AgVMgapiFJvPgZAwNWKDix56QnnYSw?e=sDzfak

Ideas?

Snippet if u don't wanna click the link. 

Swe_Mack_0-1707689920499.png

 

@Swe_Mack 

@Swe_Mack 

I forgot one quote, but the formula has to be different since the start and finish values have a time component.

I have updated your workbook.

It works perfectly! Thank you so much!

The counter is working really good.

I would like to improve it...

I dont wanna count the days in the "middle" when 2 persons swap place. 

 

As of now, I I have to se where 2 dates are meeting, and then take -1 on that count.

I have drawn lines where dates meet. On those places I wanna remove 1 from the count. 

 

Analogy.

Imagine these dates represent people on a boat. And my job is to see which people are on "duty" that day. And all date lines are when people are working, and on the last day of work, I don't wanna count them as active if that makes sense. Bc they are leaving that day. So I wanna remove the last day IF that date line meets another date coming out... Always keeping track on people on active duty. 

 

So i was thinking... if some compare function can do that. If comparing 2 dates and in that way remove one from that day. 

 

Swe_Mack_0-1708107984866.png

Swe_Mack_2-1708108209493.png

 

 

 

@Hans Vogelaar 

basically... remove the last day before doing the count on each "end" finish date should work..
-1 on each end day.. they do the normal formula

@Swe_Mack 

How about

 

=SUM((INT($B$3:$B$10)<=A15)*(INT($C$3:$C$10)>A15))

 

for the original data range, and

 

=SUM((INT($G$4:$G$51<=A15)*(INT($H$4:$H$51)>A15)))

 

for the one on the right.

 

Fill down.