Search code examples
excelsumifshour

Excel : Sum number of hours worked per day with date + time format

I'd like to sum number of hours worked per resource per day.

Example :

Resource | Task Duration (hours) | Start Date/Time | End Date/Time
-------- | --------------------  | --------------- | -------------
John     | 2:00                  | 27/09/16 14:00  | 27/09/16 16:00
John     | 2:00                  | 27/09/16 16:00  | 27/09/16 18:00
Mary     | 2:00                  | 03/10/16 09:00  | 03/10/16 11:00

What I would like to retrieve is the following :

Resource |    Day    | Hours 
-------- | --------- | ------
John     | 27/09/16  | 4
Mary     | 03/10/16  | 2

I tried with SUMIFS function but 27/09/16 is 42640 when converted to number while 27/09/2016 14:00:00 is 42640,5833333333 so it does not match.

I'm looking for a formula to match with the first 5 numbers that is the day, regardless the time.

Can you help please ?

Thanks.

Ben

Solution

  • Here is a SUMPRODUCT variation 

    =SUMPRODUCT(--(INT($C$2:$C$4)=G2),--($A$2:$A$4=F2),$B$2:$B$4*24)

    enter image description here

    I tried SUMIFS with INT, but for some reason Excel does not accept that. I don't know the reason .