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: 16 Responses to “Calculations With Time and Duration Values In Numbers”

    Lloyd Pearson
    10 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

    10 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
    10 months ago

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

    Norm
    10 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?

    10 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
    10 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.

    10 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
    10 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.

    10 months ago

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

    Perry Smith
    9 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.

    Shay
    4 months ago

    This is bugging me to death (spent hours looking for the easier thing, I'm sure). Flight duration results. 1) What is the formula to calculate the duration of 2 times resulting in H:MM 2) then adjust for time zones. Example: Departs 9:06 AM Arrives 12:56 PM, NYC to LA for example. Any help, much appreciated.

    4 months ago

    Shay: Is it the time zones that are the problem? There's no function in Numbers that calculates the amount of different between time zones, so you'd just need to enter that value manually. So subtract one time from the other, then add the duration value in another column to get the time zone shift. It is probably just easier to enter the duration manually as it is usually shown any time you look at a airline schedule anyway.

    Audrey
    4 months ago

    I created a timesheet in numbers. I need the total time worked to show in minutes, which it is except if it’s an exact hour (I.e., 9:00am-10:00am) then it shows as 1 instead of 60. I’m not sure how to change this. I do have a formula in all of my cells to calculate something else that I needed to be included in the timesheet.

    4 months ago

    Audrey: Set the format of the cell to a custom duration format and choose only "m" as the unit, no "h."

    Thomas
    4 months ago

    I used a formula to calculate a date 3 years in the future. But, I now want to take that future date and subtract one day. Any ideas what formula could accomplish this?

    4 months ago

    Thomas: Just subtract DURATION(0,1) which is 0 weeks, 1 day.

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)