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:


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:


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


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.


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")
  • Thanks so much for your help! This definitely set me in the right direction. Your solution didn't work because the 0 values above it were not durations. So, I ended up changing the formula from my first screenshot to =IF(AND(C13="GK",E13="billable"),B13−A13,DURATION(0)) to force the 0 value as a duration, which rendered the COUNTIF unnecessary. Good thing, too, because doing that broke the COUNTIF! It ended up rendering an integer and being formatted as Automatic again. So...not sure if I should edit your answer? Post my own? – Brendan Jul 1 '14 at 13:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .