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.

Want to know more about how to use Numbers on your Mac? Check out this MacMost course!

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

Lloyd Pearson

2 years 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

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

2 years ago

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

Norm

2 years 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?

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

2 years 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.

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

2 years 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.

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

Perry Smith

2 years 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

2 years 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.

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

2 years 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.

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

Thomas

2 years 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?

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

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.

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

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?

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.

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.

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

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.

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

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.

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.

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.

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.

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

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?

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