"Duration units were removed" -- Why?

Question

Level 1

40 points

"Duration units were removed" -- Why?

I have a timesheet file with several sheets, each with several tables. One sheet uses one of its tables to keep a running total of hours worked in several different categories, using a SUMIF function based on another table where the hours are logged.

I am getting unexplained behavior with the cells where the totals are displayed. These are formatted as durations, but when their value is zero they display as currency, and sometimes (but not always) they continue to display as currency when their value is non-zero, even though they are the sum of durations in the other table. There is a little blue triangle in the upper left corner of the cell, which when I click on it gives a message "Duration units were removed", with no option but to "Ignore".

What is going on? I don't want the duration units removed, and I don't want to ignore the matter -- I want to correct it! How do I do so?

I should point out that this has nothing to do with another thread on a similar topic, in which the poster had created his file on an iPad and then opened it on his Mac. I am working entirely on a Mac.

Mac Pro 8-core, Mac OS X (10.6.6), 3 GHz; 13 GB RAM

Posted on Feb 8, 2011 7:48 AM

Reply

Answer 1

Aug 29, 2017 2:42 PM in response to Jeffrey Dean

Hi Jeff

I resolved this issue by retracing the formatting of all the source cells, data that was formatted incorrectly as Automatic rather than Duration. These were in cells that fed the formula cell where the blue triangle is displayed.

Not sure why the source cell changed it's formatting definition. It seemed random in my case, but the redefining of the source cell to Duration worked.

Cheers

Reply

Answer 2

KOENIG Yvan

Level 8

41,819 points

Feb 8, 2011 10:15 AM in response to Jeffrey Dean

May you check that you are really applying only SUM function to your duration values?

Applying multiplications is the only way I was able to drop the duration format but in such case, I don't get a blue triangle or a currency result.

May you send a subset of your document to my mailbox ?
Click my blue name to get my address.

Yvan KOENIG (VALLAURIS, France) mardi 8 février 2011 19:15:42

Reply

Answer 3

Badunit

Level 7

30,708 points

Feb 8, 2011 11:09 AM in response to Jeffrey Dean

I'm with Yvan on this one. It appears your sums include something other than durations. But I, too, could not get a "duration units removed" warning when using SUMIF by itself. I could get an error triangle, though. SUM and SUMIF will not create an error when summing cells formatted as different things (currency, number, duration, etc.) if all non-blank cells are of the same type. Related to your problem, I notice that a SUM of a blank duration cell and a blank currency cell results in $0.00.

Reply

Answer 4

Feb 8, 2011 11:32 AM in response to Jeffrey Dean

Jeffrey,

I wonder if you are encountering the problem we discussed here a while back where the OP was doing something similar involving the numeric value of zero vs. the duration value of 0h. The value returned by an INDIRECT or OFFSET reference to a blank cell turns out to be a numeric zero and that's a problem in a case like yours.

It's difficult to imagine what's going on without seeing it, but the problem does sound familiar. It's the inconsistent behavior that's confusing to me.

Jerry

Message was edited by: Jerrold Green1

Reply

Answer 5

Jeffrey Dean Author

Level 1

40 points

Feb 8, 2011 11:57 AM in response to Jerrold Green1

I'm sure Jerry's got hold of the right part of the problem. After I posted the problem, I ascertained that the inconsistency resulted from part of the sum being blank, i.e. numeric zero.

The logging table works like this: category, start_time, finish_time, hours = IF(ISBLANK(finish time),"",finish_time-starttime).

The running total table works like this: total category1 = SUMIF(category,"category_1",hours), total categories2+3 = SUMIF(category,"category 2",hours)+SUMIF(category,"category3",hours), etc.

What seemed like inconsistency was that total category1 became a proper duration as soon as I had a non-zero sum in category 1, whereas total categories2+3 remained a currency value as long as I had a zero in either category 2 or 3.

So the inconsistency was a red herring. What remains is three questions: (1) Why currency? (2) [more important] Is there anything I can do to get around this counter-intuitive and undesirable behavior? (3) [much less important] Why is it that, in another cell formatted as duration, I can enter 0 and have it format as "0h 0m"?

Thanks,
Jeff

Reply

Answer 6

Feb 8, 2011 12:09 PM in response to Jeffrey Dean

Jeff,

You may have to divide and conquer. Get one part working at a time. Simplify if possible.

Your pseudocode is helpful, but sometimes the devil is in the punctuation and other minuscule detail. If you can post a screen shot and copy/paste some of your expressions here in your post, we may be able to flush out some causes.

Jerry

Reply

Answer 7

Jeffrey Dean Author

Level 1

40 points

Feb 8, 2011 12:26 PM in response to Jerrold Green1

I've taken a couple of screen shots, but I can't see how to post them.

Copying/pasting the expressions, I can give you:

=IF(ISBLANK($D3),"",$D3-$C3)

=SUMIF(26 Little Malvern Letters::$Activity,"Design",26 Little Malvern Letters :: $E)+SUMIF(26 Little Malvern Letters::$Activity,"Typesetting",26 Little Malvern Letters :: $E)

Is this helpful? (I can understand what you say about minuscule detail -- I work as a copy-editor. But I think I've got this under control: I've used the Function Browser to insert functions, and selection to replace boilerplate parameters. The only place where I could have made a mistake would have been in spelling "Design" and "Typesetting", but I've double-checked those against the value list in the Activity column.)

Jeff

Reply

Answer 8

KOENIG Yvan

Level 8

41,819 points

Feb 8, 2011 12:31 PM in response to Jeffrey Dean

I'm not a sooth sayer so, I feel unable to guess what may be a very small oddity somewhere.

I repeat : may you extract a subset of the document which demonstrate the odd behavior ?

If you may, send it to my mailbox.
Click my blue name to get my address.

Yvan KOENIG (VALLAURIS, France) mardi 8 février 2011 21:31:40

Reply

Answer 9

Jeffrey Dean Author

Level 1

40 points

Feb 8, 2011 12:43 PM in response to KOENIG Yvan

File sent separately to Yvan.

Reply

Answer 10

Feb 8, 2011 12:52 PM in response to Jeffrey Dean

To display a screen shot, upload it to a photo sharing server (Picasa, etc.) and paste the HTML sharing code here.

Jerry

Reply

Answer 11

KOENIG Yvan

Level 8

41,819 points

Feb 8, 2011 1:36 PM in response to Jeffrey Dean

The formula was adding durations and a zero number.

=SUMIF(26 Project title::$B,"Design",26 Project title :: $E)+SUMIF(26 Project title::$B,"Typesetting",26 Project title :: $E)

I edited it this way :
=IF(COUNTIF(26 Project title :: B,"Design")=0,DURATION(,,,0),SUMIF(26 Project title::$B,"Design",26 Project title :: $E))+IF(COUNTIF(26 Project title :: B,"Typesetting")=0,DURATION(,,,0),SUMIF(26 Project title::$B,"Typesetting",26 Project title :: $E))

and now it behave flawlessly.

The behavior of the original formulas was perfectly consistent : they did what the O.P. asked them to achieve.

When the column B of the table 26 Project title contain the string Design and the string Typesetting, the two SUMIF deliver durations and the sum is a duration.

When the column B of the table 26 Project title doesn't contain the string Design or/and doesn't contain the string Typesetting, at least one of the SUMIF deliver a zero so the sum drops the duration kind and inherit of the format of the cell just above : currency .

Yvan KOENIG (VALLAURIS, France) mardi 8 février 2011 22:36:00

Reply

Answer 12

Jeffrey Dean Author

Level 1

40 points

Feb 8, 2011 2:59 PM in response to KOENIG Yvan

Now that it is explained, I can understand the solution. I am disappointed that there is nothing in the documentation that makes this sort of thing clear. The documentation for functions is extremely terse and implicitly expects a level of knowledge and experience that I simply don't have (along with a great many other users, I'm sure). What's obvious to you wasn't at all obvious to me. I am extremely grateful for your taking the trouble to help me; I only wish I could have found better guidance in the documentation.

Many thanks,
Jeff

Reply

Answer 13

Barry

Level 9

60,023 points

Feb 8, 2011 11:20 PM in response to Jeffrey Dean

Jeffrey Dean wrote:
Now that it is explained, I can understand the solution. I am disappointed that there is nothing in the documentation that makes this sort of thing clear. The documentation for functions is extremely terse and implicitly expects a level of knowledge and experience that I simply don't have (along with a great many other users, I'm sure). What's obvious to you wasn't at all obvious to me. I am extremely grateful for your taking the trouble to help me; I only wish I could have found better guidance in the documentation.

Hi Jeff,

Shortcomings in the documentation are things that should be posted as Numbers Feedback, and sent to Apple, rather than to the Numbers users who read and respond in this forum. You'll find a Provide Numbers Feedback item in the "Numbers" menu. Please use it to submit a request for improvements in the iWork Formulas and Functions User Guide.

Regards,
Barry

Reply

Answer 14

Jeffrey Dean Author

Level 1

40 points

Feb 9, 2011 3:03 AM in response to Barry

Thanks, Barry. I've done that. I'm sorry if you get annoyed by people using the forums for things that ought to be sent to Feedback, but if you'd read the whole thread you'd have seen I had a problem to solve. Yvan helped me solve it, and I was trying to express my gratitude for his assistance while explaining why I had needed it in the first place. Please have a little patience with those of us who are experienced enough to try to do something a little complicated, but not experienced enough to understand what went wrong when it doesn't work.

Reply

Answer 15

Feb 9, 2011 8:36 AM in response to Jeffrey Dean

In creating the documentation, Apple walks the fine line between tedious and incomplete. Well, not so fine, I guess. Since iWork is the all-in-one suite for the rest of us, it's understandable that they would err on the side of comfortable reading.

Jerry

Reply