0

I have an interesting one which I am really struggling to resolve and feel this should be possible using SUMIFS somehow!

I have the following (as per the image):

Screenshot

I have a number of tasks to complete on different days. Each task has a separate row and each task has a specific duration.

On "Day 1" I enter the duration in column C for my first task which automatically calculates column D's answer (simply B+C=D).

  • Task 1: 09:00 - 09:30

On "Day 2" I have three tasks to complete:

  • Task 1: 09:00 - 10:00
  • Task 2: 10:00 - 12:00
  • Task 3: 12:00 - 22:00

What I would like to do in the second table (F-H) is workout the total hours in-hours (between 09:00 and 17:00) and the total time out-of-hours (hours outside of 09:00-17:00) as per columns G and H.

Formula for column F extracts all unique day numbers:

=IFERROR("" & INDEX($A$3:$A$12, MATCH(0,COUNTIF($F$1:F1, $A$3:$A$12), 0)),"")
Improve this question
4
  • to solve what you present I would calculate in-hours first, then I would take total hours less in-hours to get out-of hours. For more complicated cases would require more complicated formulas, or an alternative way of storing data so that the formulas become simpler.
    – gns100
    Commented Dec 14, 2022 at 0:06
  • Unfortunately this is a more complicated case as tasks could start at any time. So I need to be able to determine how much of the task is in hours and how much of the task is out of hours. It appears simple on the surface but scratch a little deeper and it’s a lot more complicated. I appreciate you getting back to me, thank you!
    – Ramma
    Commented Dec 14, 2022 at 7:41
  • Can you add the helper columns for left table to get the Duration hh:mm value for In-Hours and for Out-of-Hours? Then you can easily use SUMIF function to get sum for each day, and then add Minute()/60+Hour() formula to get results.
    – Emily
    Commented Dec 14, 2022 at 9:00
  • This is exactly the route I've gone down. I've created a helper column that works out the out of hours which means that the in-hours is total minus out of hours. I don't think it's pretty but it's effective!
    – Ramma
    Commented Dec 14, 2022 at 12:26

1 Answer 1

Reset to default
0

I have now resolved this by adding an additional helper column allowing me to calculate the out-of-hours aspect. I can then SUMIF the total out of hours and to get my in-hours answer, I just subtract the out-of-hours result from the total hours.

Formula to work out my out of hours (probably a more elegant way of doing this...) - using column E for example for my helper column:

=SUM(IF(D3<TIME(9,0,0),D3-B3,IF(B3<TIME(9,0,0),TIME(9,0,0)-B3,0)),IF(B3>TIME(17,0,0),D3-B3,IF(D3>TIME(17,0,0),D3-TIME(17,0,0),0)))

To calculate "Day" (Column F):

=IFERROR("" & INDEX($A$3:$A$12, MATCH(0,COUNTIF($F$1:F1, $A$3:$A$12), 0)),"")

To calculate In-Hours (Column G) - I also round up to nearest 2:

=IF((SUMIF($A$2:$A$12,F2,$C$2:$C$12)*24)=0,"",CEILING((SUMIF($A$2:$A$12,F2,$C$2:$C$12)*24)-H2,2))

To calculate In-Hours (Column H):

=IF((SUMIF($A$2:$A$12,F2,$E$2:$E$12)*24)=0,0,SUMIF($A$2:$A$12,F2,$E$2:$E$12)*24)

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .