When a function computes 0, how do I make the cell display 0hr 0min

Question

Level 1

0 points

When a function computes 0, how do I make the cell display 0hr 0min

I have a function in a column of cells, and format to display the calculated value in duration "hr min". So long as the function calculates a positive value, that value is displayed correctly in "hr min". However, when the function calculates a value of 0, that value is displayed simply as "0", instead of "0hr 0min". When I check the format of that cell, it shows automatic, instead of duration.

I have found a workaround by placing that function within the duration function.

iPad Air, iOS 8.2, null

Posted on Apr 8, 2015 12:06 AM

Reply

Answer 1

jaxjason

Level 5

5,047 points

May 6, 2015 1:49 PM in response to JT Varsity

Not meaning to bump, but I think this deserves clarification for anyone trying to do duration calculations. Its one of the function sets that is not a duplicate from other spreadsheet programs and hard to grasp for some people coming over from XL.

In order for any function result or calculation result to work as a duration, you must use the DURATION function to let the program know what unit your number is meant to represent, i.e. is it 25 hours, minutes or seconds? You dont need to break the units down yourself. Meaning if you have a cell that could range from 0 to 10000 and its representing minutes, just use =DURATION(,,,B2,,) (the app will show little bubbles with "Weeks", "Days", etc... to show where you need to put your formula or cell reference). The program will convert 10000 minutes into the correct units you designated when you formatted the column.

Jason

Reply