How to handle Numbers’ automatic Data Formatting

iworknumbers

I'm trying to put together a sheet that calculates hours I work. One thing I want to do is calculate hours that are billable. So I have something like this:

Formula

Things that aren't billable or are already billed go to 0.

However, I'm having a problem with either the zeros that are generated or the field that adds them all together:

Error

"Durations can't be compared to other data types." Here's my formula for the cell:

COUNTIF(range,">0")

So, either the second value in the COUNTIF is giving me problems or the zeros above are (which is possible, since doing a simple SUM with a 0 value won't work either).

How do I deal with this? If I try to set the 0 cells or the sum cell at the bottom to the Data Format "Duration," it overrides my selection.

Is there a way to force a data format onto a cell with a formula?

If this is more suited to Super User, feel free to transfer.

Best Answer

Without access to the spreadsheet that you've shown, I've attempted to recreate this myself. It appears that there are two things wrong with the spreadsheet above:

  • The test-array for the countif is of Data Format Automatic. This means that the zeros are not of format Duration and can't be compared by the countif.

    To fix this, select the entire array and choose Duration from the Data Format list.

  • The countif's condition is of format Number whereas the test-array is of format Duration.

    To fix this, use the following for the countif cell:

    =COUNTIF(G2:G11,">0h 0m")
Related Question