formula failure to operate

[delboy1616]

Hello everyone, I have noted that I cannot post attachments which is a shame as I could demonstrate my issue more clearly. Cell L15 =SUMIFS(H:H,B:B,">="&H15,B:B,"<="&K15)*24 it is devised to sum the total hours for the month or at least too the cut off point which in my case is the 19/12/2017. This has worked as designed with no apparent issue.

Then I use a simular formula in Cell L16 =SUMIFS(H:H,B:B,">="&H16,B:B,"<="&K16)*24 this time using data input from 20/12/2017. Why will this cell not calculate the data from the 20th onwards? or am I missing something obvious? I am not able to submit a sample due to a permissions rule ( not sure why)
Thanks.

 

[joeu2004]

I have noted that I cannot post attachments which is a shame as I could demonstrate my issue more clearly.

Upload an example Excel file (redacted) to a file-sharing website (e.g. box.net/files) and post the public/share URL in a response here. Test the URL, being careful to log out of the file-sharing website first.

Some participants complain. They don't have to make use of the upload. But as you say: it might be easier to understand the problem with a concrete example; and sometimes, the devil is in the details that we cannot see if you simply post a table of data and formulas here.

Cell L15 =SUMIFS(H:H,B:B,">="&H15,B:B,"<="&K15)*24 it is devised to sum the total hours for the month or at least too the cut off point which in my case is the 19/12/2017. This has worked as designed with no apparent issue.

Then I use a simular formula in Cell L16 =SUMIFS(H:H,B:B,">="&H16,B:B,"<="&K16)*24 this time using data input from 20/12/2017. Why will this cell not calculate the data from the 20th onwards?

As you say, we can only (wild) guess without access to the actual data.

To begin, what do you mean by "not calculate": return zero, or return an Excel error (e.g. #VALUE )?

If you mean it returns zero, look carefully at the contents of H16 and K16 to be sure that Excel recognizes them as dates.

Does ISNUMBER(--H16) and ISNUMBER(--K16) return TRUE?

I use --H16 and --K16 just in case the cell values are text; the "--" converts to numeric, if possible. That is not necessary for SUMIFS to work. But it ensures that Excel recognizes them as dates.

-----

It seems odd that the sum of H:H is time (since you multiply by 24), but H15:H16 seem to contains dates, I presume, since you mention the dates 19/12/2017 and 20/12/2017.

At the very least, post the values in H15:H16, K15:K16 and some rows of H:H that you believe SUMIFS should accumulate for both examples that you mention (one that works; one that doesn't).

It would be prudent to post the data in two forms: first, how they appear in the cell; second, how they appear when formatted as Scientific with 14 decimal places. The latter allows us to understand the cell contents independent of (and unmodified by) the cell format.

 

[joeu2004]

When I tried to reply to your PM, I get the following error: "delboy1616 has exceeded their stored private messages quota and cannot accept further messages until they clear some space".

Referring to the Excel file that you provided in a PM, my response is....

You need to give your SUMIFS logic a lot of thought. And then explain it in English terms, if you still need some help.

It is not clear to me what your intent is.

In your design, column B contains dates, column H contains time, and columns J and K contain dates in some rows.

So when you write SUMIFS(H:H,B:B,">="&H15,B:B,"<="&K15) in column L, the logic says:

sum times in column H where dates in column B are not less than the time in H15, and dates in column B are not greater than the date in K15.

The second condition makes sense: you are comparing dates with a date.

But the first condition does not make sense: you are comparing dates with a time.

In Excel, all dates are greater than any time (less than 24 hours). That is why the formula in L15 appears to work insofar as it returns some numeric value. It happens to be the correct numeric value. But that is only a coincidence due to the fact that J15 (starting date) is the same as the earliest date in column B.

In contrast, the formula in L16 does not work because H16 is the null string (""). In Excel, all dates (numeric values) are less than any text, including the null string. So the first condition is always FALSE (except for rows 5:7, where column B is empty; Excel treats the empty cell as equal to the null string). Consequently, SUMIFS returns zero.

I suspect the intended logic is SUMIFS(H:H,B:B,">="&J16,B:B,"<="&K16). In that case, L16 would result in 39.5.

 

[delboy1616]

My concept of the timesheet is to record individual tasks. Then to sum these tasks into a monthly activity according to the cutoff date which occurs near the end of the month. The cutoff dates change each month hence the need for column k.

One thing I can’t understand is the failure of L16 to produce the result of number of hours worked in a month. It appears to work fine in L15 so why not in L16?

thanks Del

 

[joeu2004]

One thing I can’t understand is the failure of L16 to produce the result of number of hours worked in a month. It appears to work fine in L15 so why not in L16?

I believe I explained that adequately.

I wrote: ``the formula in L16 does not work because H16 is the null string (""). In Excel, all dates (numeric values) are less than any text, including the null string. So the first condition is always FALSE (except for rows 5:7, where column B is empty; Excel treats the empty cell as equal to the null string). Consequently, SUMIFS returns zero.``

In contrast, I wrote: ``In Excel, all dates are greater than any time (less than 24 hours). That is why the formula in L15 appears to work insofar as it returns some numeric value. It happens to be the correct numeric value. But that is only a coincidence due to the fact that J15 (starting date) is the same as the earliest date in column B.``

But based on your intent described below, I question whether the value in L15 is indeed "correct" (i.e. what you should expect).

My concept [... is ...] to sum these tasks into a monthly activity according to the cutoff date which occurs near the end of the month. The cutoff dates change each month hence the need for column k.

What do you mean by "month"? Calendar month of the cutoff date (e.g. Dec)? 4-week period ending with cutoff date? 30-day (31? 28? 29?) period ending with cutoff?

Provide the start and cutoff dates that you intend for the L15 and L16 calculations.

And provide the values in L15 and L16 that you expect, by summing the applicable times in column H manually.

-----

Why not use column J in the first SUMIFS condition, as I demonstrated? Column J is titled "Start of Mth".

And for L15, why is the "month" effectively from 11/10/2017 to 12/19/2017 (K15)?

That is 39 days. For any of my definitions of "month", L15 would be 43.5, not 50.

On the other hand, I note that J16 is 12/20/2017. Does each "month" start on the day following the previous cutoff date?

The bottom line is as I wrote before: perhaps the SUMIFS expression should be ``SUMIFS(H:H, B:B, ">=" & J16, B:B, "<=" & K16). In that case, L16 would result in 39.5``.

Why isn't that the correct answer?

Alternatively, the SUMIFS expression might be one of the following, based on my definitions of "month" above.

Same calendar month as the cutoff date:
SUMIFS(H:H, B:B, ">=" & EOMONTH(K16,-1)+1, B:B, "<=" & K16)

4-week period ending with the cutoff date:
SUMIFS(H:H,B:B,">="& K16-28, B:B, "<=" & K16)

30-day period ending with the cutoff date:
SUMIFS(H:H,B:B,">="& K16-30, B:B, "<=" & K16)

 

[delboy1616]

Hi, after reading this line SUMIFS(H:H, B:B, ">=" & J16, B:B, "<=" & K16) I realised what I had missed, I couldn't see the wood for the trees. I made the change and the formula started to function as I intended using J in the formula. Thank you for your support you were helpful.

Thanks
Del