Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Creating Custom Time Durations

I have an app that reports daily data from a CPAP machine. I am tracking what it reports in a spread sheet. One usage is time on the machine, for example it might report 7 hrs 57 mins. If I enter it as 7.57 it converts the .57 to 34.20 minutes. That is not accurate for determining the average time over a period of time. If I enter the time as 7' 57" it is not recognized to report the average time.

Posted on Oct 16, 2020 4:35 AM

Reply
Question marked as Best answer
dtryon9981 wrote:

Barry,
Is there a way to display time in h & m and then also to be able to get an average over a group of entries? Or could I make a daily entry entirely in minutes and then display an average in h & m?


Did you see my screenshot above which shows several values of h & m and then takes an average? Here it is again:






SG


Posted on Oct 18, 2020 7:11 AM

12 replies

Oct 16, 2020 4:45 AM in response to dtryon9981

When you say "time" do you mean a point in time, or do you mean a segment of elapsed time?


If you mean the latter, then Numbers has a data format called Duration. You would enter 7 hrs 57 min of elapsed time as 7h 57m.

For seconds you would use s.


You can display that value in various ways, as shown here:




You can add or average duration values you have entered in a range of cells, here in column E:



SG

Oct 16, 2020 7:27 AM in response to dtryon9981

dtryon9981 wrote:

it is displaying the numerals but not the "h" "m"


As shown in my screenshot you can choose different ways to display the duration. The 0:00 choice would not show the "h" and "m" even if you (correctly) enter the duration using the "h" and "m" input shorthand. If you change your choice in the dropdown shown in the screenshot and are still having trouble perhaps you could post a screenshot.


SG





Oct 17, 2020 6:22 AM in response to dtryon9981

Lets start from the beginning pls.


In this screen shot I have attempted to set the data format to the custom units.



Now in this screen shot you will see that the data format is not holding and reverts to automatic. In other attempts I have selected the entire column and set it to the custom format and then after that step I would select the header column name to text. The issue at the moment is that the custom data format is not being held.

Oct 17, 2020 7:20 AM in response to dtryon9981

How did you enter the data in the "Time" (Shouldn't it really be "Duration" or "Time Elapsed") column?


If the 2 6 represents 2 hours and 6 minutes then did you key it it in as 2h 6m ? In other words did you use the Numbers input notation that tells it you mean a duration?


You might try selecting those cells and hitting <delete>. Then reenter the duration using the input notation.


SG



Oct 18, 2020 12:26 AM in response to dtryon9981

dtryon wrote:

"I assumed that entering 2 space 6 or 2.6 would display as 2h 36m."


Your assumption was incorrect. 2 6 is not recognised by Numbers as a duration value, and willl be treated as a text string. 2.6 will be recognised as a number, but not as a duration value. 2h 36m is a duration value and is the safest format to use when entering durations.

2:36 is also recognized as a duration value, and is one of the duration display formats offered in Numbers. but that format is also used to express the 'time of day' part of a Date and Time value, making it a less safe format to use to enter duration values expecting them to be recognized as such.


"No matter which I use even with the field set to duration it reverts to automatic."

This is expected behaviour in Numbers—if you enter a value incompatible with the accepted formats of the type of value the cell is set to expect, the data format setting of the cell will revert to Automatic, and the value will be accepted and displayed using the value type Numbers expects to receive in that format.


"Then what is the purpose of the custom format? I could simply enter 2h 6m as text."

You could enter 2h 6m as text in a cell whose data format is set to text, and it would be treated as text in that cell. It's default alignment would be to the left side of the cell, it would not contain a duration value, and it would likely not be useable in arithmetic calculations. The purpose of the specified duration format is to ensure that Numbers recognizes and treats the 2h 6m entry as a duration of two hours and six minutes, and can use that value in calculations.


BTW, 0h 0m is a specified format for duration, included in the list of formats directly choosable in the Cell format inspector.

Custom formats are not on the list, but are created by the user, using the tool that opens when the user chooses Create Custom format… in the Data Format menu in the Inspector.


Regards,

Barry

Question marked as Best answer

Oct 18, 2020 7:11 AM in response to dtryon9981

dtryon9981 wrote:

Barry,
Is there a way to display time in h & m and then also to be able to get an average over a group of entries? Or could I make a daily entry entirely in minutes and then display an average in h & m?


Did you see my screenshot above which shows several values of h & m and then takes an average? Here it is again:






SG


Creating Custom Time Durations

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.