Chart with time durations do not work

From the Numbers '09 User Guide:

"Chapter 7 Creating Charts from Numerical Data"

Note that the type of data is specified—"from Numerical Data."

You'll need to convert your Duration data in column D to Numerical data.

Current formula: =C-B

Revised: =DUR2DAYS(C-B)

Regards,

Barry

This helped me too. I am trying to create a sleep log. Perhaps you can help with another related issue. I have two columns, one for sleep time, and one for wake time. In another table, I am calculating the duration by subtracting the one from the other, however for future dates the function is returning zero, but I would prefer for it to just be blank if both the cell references are blank.

In other words, I only want a value in the duration table column if I have entered both a sleep time and a wake time in the other table. Is this possible? If so, how do I do this? I have tried SUMIF but didn't do the trick (or I did it incorrectly). It still returned a zero for the future dates.

Hi George,

Sleep time in column B, wake time in column C of Table 1, Duration of sleep in column B of Table 2:

B2: =IF(AND(LEN(TABLE 1::B)>0,LEN(TABLE 1::C)>0),TABLE 1::C-TABLE 1::B,"")

Note: The 'times' in Table 1 are Date and Time values, with the date set to the date you make the entry (unless you include a date in the entry). You will get correct results IF each time is entered on the date that it occurs. eg. If you entered a sleep time of 8:00 PM on August 15, 2012, then entered the wake time, 8:00 AM on August 16, you would get a (correct) result of 12h 00m.

Regards,

Barry

Barry,

Thanks! Worked perfectly. I'm trying to use your example a step further. In order to graph the results, I need to convert them to duration using the DUR2HOURS function.

Based on your system above, I'm using =DUR2HOURS(TABLE 2::B) [I think that's correct, my chart has column names).

I deduced how you achieved the If/Then conditons using >0 and the "" to return blank data if not True. But my formula is generating a syntax error:

=IF(DUR2HOURS(TABLE 2::B)>0),DUR2HOURS(TABLE 2::B),"")

Any insight on what I am doing wrong here? Thanks!

Hi George,

The most common cause I've seen for syntax errors in complex formulas is an unequal number of left and right parentheses. (and/or parentheses in the wrong location(s)).

Here's your formula:

=IF(DUR2HOURS(TABLE 2::B)>0),DUR2HOURS(TABLE 2::B),"")

Here's a corrected version of that formula, to which I've added colour to emphasise the opening and closing parentheses for each function:

Copying the formula containing the error into a word processor, then picking out the various components using colour and/or other formatting can help track down syntax errors. In this case, it left me with an unpaired closing parenthesis after the zero, which I removed before copyng the image above.

Regards,

Barry

Barry,

First, your color-coding is genius and should be added to Numbers itself. Makes troubleshooting much easier!

I've applied your correction to no success. Because Numbers adds the colored "pill" outline, it confuses me when I see the plain text versions (does the "pill" include the parenteses or not?)

So I've linked an image of the actual formula as I see it for a single cell:

Again, I appreciate your time and help on this!

I'm not sure if that image link is showing up so here is the link to the image file:

http://cl.ly/KeYD

Hi George,

As you suspected, your inserted image did not come through. Here it is, from a screen shot of the file linked in your follow-up message:

The lozenge contains only the cell (or range) reference. References entered as text generate the lozenge only if there are no syntax errors to that point in the formula.

You note only that this is 'not working.' Does the formula generate an error flag (red triangle)? If so, what is the error message presented when you click (once) on the triangle?

I suspect, from trying DUR2HOURS() on an empty cell, that the message is ' The function "DUR2HOURS" expects a duration, but found "." '

Since the purpose of your formula is to convert duration values above zero hours to a number and to return a null string for noo-duration values, you might try this:

IFERROR(DUR2HOURS(Total Sleep::'11/3/12"),"")

This formula will return a zero value if the target cell contains a zero hours duration value, but will throw an error if the cell contains a non-duration value. IFERROR is used as an error trap t catch this error, and return an empty string should an error occur.

Regards,

Barry

Barry,

I was not aware that the red triangle when clicked offered up more details, and you are correct -- it informed me that it was expecting a duration but the cell contained a string.

Your solution completely worked, thanks once again. I'm still new to spreadsheets and I know my lack of awareness of what kinds of functions are available can hold me back.

Thanks again for the help, hopefully I can repay the favor if you ever need Photoshop or Illustrator help, more up my alley than Numbers 🙂

George,

Apple provides a couple of excellent resources with Numbers '09:

The Numbers '09 User Guide is a general guide to the application. The first three chapters should be considered 'required reading.' After those, check the titles, skim the first page or two, then consider it a reference to use when you need to know/learn more about a particular topic.

The iWork Formulas and Functions User Guide lists all the functions supported in Numbers, with a description of each, its syntax, and one or more examples of how the function may be used in practice.

Both guides are searchable PDF documents. Both may be downloaded via the Help menu in Numbers '09.

Regards,

Barry

Barry,

I've poked around a bit in the manual, but I think you are right about sitting down and reading. Thanks for the tips on the chapters to focus on.

I've only really gotten into spreadsheets recently, and have discovered I really enjoy manipulating data 🙂

Thanks again for all your help!