Forum Discussion

Rwogan's avatar
Rwogan
Copper Contributor
Jul 19, 2022

How can I count overall staffing time per hour per day?

So, i have my staff log in and log out time, but i need to show it as total mins worked per hour block. The problem I am encountering is if someone finishes mid hour as i will get the value incorrec...
excel
Formulas and Functions
tauqeeracma's avatar
tauqeeracma
Iron Contributor
Aug 11, 2022

Hi Rwogan 

 

You can achieve your desired results by using the below formula.

=IF(AND(MATCH($B4,$F$2:$U$2,1)=MATCH($D4,$F$2:$U$2,1),MATCH($D4,$F$2:$U$2,1)=F$1),($D4-$B4),IF(MATCH($B4,$F$2:$U$2,1)=F$1,(F$3-$B4),IF(MATCH($D4,$F$2:$U$2,1)=F$1,($D4-F$2),IF(AND(MATCH($B4,$F$2:$U$2,1)<=F$1,MATCH($D4,$F$2:$U$2,1)>=F$1),TIME(1,0,0),""))))

Please refer to the attached sample file wherein I have updated this formula for your reference.

 

I have observed some inconsistency in column E (duration). For instance, in row 11, the actual duration should be 30 minutes and 52 seconds, but it appears as 30 hours and 52 minutes. Anyhow, I have also put a formula for this column.

 

I hope it will be helpful.

 

Please let me know if it works for you.

 

Thanks

Tauqeer

Resources