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