2

I'm trying to keep track of my worked hours and translate it in billable days (I bill on a day basis, not hours) while calculating my monthly hours balance.

I've got cells corresponding to each day of the month in which I place my worked time with format hh:mm. From there I was able to sum up these hours, giving a total A.

Now I'm trying to get in a cell the number of billable days knowing that:

  • 0 (or empty cell) <= x < 03:00 = not billable
  • 03:00 <= x < 06:00 = 0,5 day billable
  • 06:00 <= x = 1 day billable

where x is the worked time each day (in the first one x will probably always be an empty cell or 0 as I don't expect to be working for free...)

I've explored quite some conditional sum functions but any resource I can find is still about summing up (possibly with a condition) the values in the cells self, not 0,5's and 1's. I actually have no idea if that's even possible to do that through excel formulas or if I'll need to use VBA for this.

Laurent S.
  • 135
  • 1
  • 8

2 Answers2

2

You can also use the SUMPRODUCT function:

=SUMPRODUCT((A1:A4<(6/24))*(A1:A4>=(3/24)))*0,5+SUMPRODUCT((A1:A4>=(6/24))*1)*1

enter image description here

It is not so difficult to understand. The first part SUMPRODUCT((A1:A4<(6/24))*(A1:A4>=(3/24)))*0,5 does the following:

It goes through range A1:A4 and for each cell that is smaller 6/24 (hours are considered a fraction of the whole day in Excel), it will add TRUE to an array. Then you do the same again but check if it is larger og equal 3/24 and build a second array with TRUE values where the criteria matches. Then, both arrays are multiplied row by row. TRUE*TRUE results in 1 (while TRUE*FALSE would be 0). All the one’s and zero’s will be added up. In the end you just need to multiplay the result with 0,5 – since you want each such days to count for 0,5 billable days.

The same principle for the second SUMPRODUCT.

I hope that helps you.

Laurent S.
  • 135
  • 1
  • 8
Michael Wycisk
  • 255
  • It would be nice if someone with a higher reputation could edit my post, so the picture appears in it. Thanks! – Michael Wycisk Dec 06 '19 at 10:25
  • Thx Michael! I edited the post to put the picture inline but my edit still need to be reviewed – Laurent S. Dec 06 '19 at 10:36
  • Thank you @LaurentS. I just approved it. Looks better now. – Michael Wycisk Dec 06 '19 at 10:38
  • 1
    I approved this answer because there's no extra column involved and I got an explanation of what was happening there (I've been developing for years and still excel syntax and functions look like magic to me) so I can maybe re-use it in the future for something different. – Laurent S. Dec 07 '19 at 15:55
  • Thanks @LaurentS. feedback is always highly appreciated :-) – Michael Wycisk Dec 07 '19 at 15:57
1

Most obvious way is to add a helper column where you calculate billable days, than summarise this helper column.

An example formula for your helper column:
=IF(A2<TIME(3,0,0),0,IF(A2<TIME(6,0,0),0.5,1))

Or you can use different array formulas to perform the calculation in one steps.
As those are array formulas you need to press CTRL+SHIFT+ENTER after typing them.

  • =SUM(IF(A2:A19<TIME(3,0,0),0,IF(A2:A19<TIME(6,0,0),0.5,1)))
  • =SUM(MATCH(A2:A19,{0,0.125,0.25},1)-1)/2

enter image description here

Máté Juhász
  • 21,451