Calculations With Time and Duration Values In Numbers

Dates, times and duration values behave differently in Numbers and other spreadsheet apps than other values. Learn what makes up time and duration values and how to use them in formulas and functions.


Comments: 10 Responses to “Calculations With Time and Duration Values In Numbers”

    Lloyd Pearson
    5 months ago

    I get adding hours and minutes : fx ($A3+B3) with result in column C
    A2=3h 40m B2=1h 12m C2=4h 52m
    What formula would you use in column C when the result is more than 24h?
    This would be used for a running total of hours i.e. 34h 21m and not 1d 10h 21m
    After the initial input to A2 I use the result in C2 for A3

    5 months ago

    Lloyd: Same formula. Just different formatting. Set the format of the cell to duration, custom, and only select d and m. I show exactly this starting at around 8:15.

    Lloyd Pearson
    5 months ago

    Thanks Gary, totally missed the h m duration part. As always great videos.

    Norm
    5 months ago

    This video was very helpful in summarizing a table of data containing dates of sale (ColumnA) and sale prices of homes (Column B). I wanted to calculate the number of homes sold and the average sale price for that year. I did that using the Year, Countif and Averageif functions. However when I tried to determine Average Sale Price for a range of years. I got an error message. How do you identify or show a range of dates or years in the formulas, ie years 2005 to 2010?

    5 months ago

    Norm: Hard for me to guess where you could be going wrong. Look at the error message and try to fix the issue it shows. Experiment with it until you figure it out.

    Norm
    5 months ago

    Using the Averageif function to determine Average Sale Price for certain year this would be my formula AVERAGEIF(X2:X122,"2021",Y2:Y122) How would I show the condition for the years 2015 to 2021 instead of only 2021? I tried using : to show the range, but nothing seem to work.

    5 months ago

    Norm: Using a colon for a range is just for a range of cells. To do this, you want to create a new column (you can hide it later if you like) and use AND to test the year. Like AND(X2>=2015,X2<=2021). Paste that into all cells in that new column. So you have a true in rows where the year is 2015 to 2021. Then use AVERAGEIF testing that new column against it being "true."

    Simon Thornton
    5 months ago

    I’ve got a list in a spreadsheet with numerous friends birthdays. What calculation/s do I need to add to a Numbers spreadsheet to caluoate their current age based on todays date, which I’ve got in A1 for example, as 8 Feb 2022, or TODAY. I need their ages to update as and when they have a birthday so it is reflected on my spreadsheet when ever I open it.

    5 months ago

    Simon: Just subtract their birthday from TODAY. The result is a duration.

    Perry Smith
    4 months ago

    Slightly off topic, I hope that is ok. The old HP calculators (HP-48) totally understood units. So, the calculation I want to do today, I want to determine how long it will take to transfer 6TB of data when the transfer is 15MB/s. In the HP days, I could have entered 6 TB in one cell, 15 MB/s in another cell, and then in the third do A / B and it would have calculated the duration that the transfer would take providing me with proper units of seconds -- which could be converted.

Leave a New Comment Related to "Calculations With Time and Duration Values In Numbers"

:
:
:
0/500 (500 character limit -- please state your comment succinctly and do not try to get around this limit by posting two comments)