2/3/22
9:00 am

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.

Check out Calculations With Time and Duration Values In Numbers at YouTube for closed captioning and more options.

Video Transcript: Hi, this is Gary with MacMost.com. Let's take a look at using the date, time, and duration values in Numbers.
MacMost is brought to you thanks to a great group of more than 1000 supporters. Go to MacMost.com/patreon. There you can read more about the Patreon Campaign. Join us and get exclusive content and course discounts.
Now usually in Numbers when you have values they're just regular numbers. Sometimes those numbers represent money. Other times you've got labels for things. But occasionally you're going to use the values, like dates, times, and even durations. These work differently than regular numbers especially when it comes to performing calculations on them. 
So a typical date value would look something like this. It's just the month, day, and year. Of course depending on the part of the world you're in you might actually see this as day, month, year rather than month, day, year. If you look at this right here you'll see the actual value here at the bottom. So you want to look for the date value in this case here. If you see something else then perhaps Numbers is interpreting what you typed as a label and not an actual date. Now you can also include times. So I can do a date like this and also a time like that. Now you can see here the actual value shows you the date and the time. I can work with the formats here as well changing the time format and the date format to show it like I want. It's important to realize that dates and times are the same type of value. When you say February 2nd, 2022 you're really saying February 2, 2022 at midnight. You include a time and then it's not midnight it is that specific time like 8:00 a.m. right here. 
Now typically you cannot perform calculations on times. There are exceptions that we'll look at in a few minutes. Time is basically just a label. So if you have a spreadsheet that is showing the amount of items sold in a day you may have a label that's in one column that has the date. It identifies that row as sales belonging to that date. If each row represents a specific sale then you may have a date and time of that specific sale. So we just use these to organize and to know what each row is representing. We're not using them for calculations. 
So while dates and times are moments in time, durations are lengths of time. For instance a duration may be something like three days. How would you enter three days in Numbers? Well, there's special notations for doing that. You would type 3 and then the letter d. That represents days. When I hit Return you could see it's not treating that as a label. That would be left justified. It's treating it as a duration. If I select it it doesn't say Text down here. It says Actual, 3d. Three days. There are other letters that you can use as well. For instance, 6s is six seconds. 7m is seven minutes. 8h is eight hours. We've seen days already. Something like 2w is two weeks. You could also have something like 400ms which represents milliseconds. You can combine these in anyway you want. So, for instance, you could have 2w and 4d like that. You could have 5h 6m 23s like that. There are no months or years because months and years aren't consistent lengths of time. Months could range from 28 to 31 days. Years could be 365 or 366 days. Only weeks and days, hours and minutes, seconds and milliseconds are consistent units of time.
Now you can perform calculations on durations very easily. You could add and subtract them. So for instance if I have 7s here and I have 5s here I can have a formula that adds one and then the other. The result will be 12s. You can also subtract. So I could subtract one from the other. You could see 7s minus 5s is 2s. You could do this with any number of units. 1h and then minus 5s result is 59m 55s. I could do say 5d and then subtract from that 2d to get 3d. Or I can subtract from that 12h and get 4d 12h. 
Now you can't multiply durations. That doesn't make sense. Multiplying something like say 3d times 2d doesn't get you six days. It gets you technically 6d squared which doesn't make sense. But Numbers will figure out what you mean. So if I do multiplication like that you can see it gives me the answer 6 but it gives me a little warning there. The proper thing to do would be to multiply the duration by a real number. So 3d times 2 will give me six days. 
Now as I said before you can do some calculations on times and that's when you use a duration as the other part of the calculation. For instance if I had a date like this and maybe a specific time and I wanted to add to that 5h I could. So I could do 5h here and then the formula of this time plus this duration will give me February 2 at 1:00 p.m., five hours after 8:00 a.m. I could also subtract and see that 3:00 a.m. was five hours earlier. I could do all sorts of things. Like, for instance, change this to subtracting 3d. So I could see January 30 was three days earlier. 
But what if I wanted to calculate the next month. One month after this time. Well, I could do 30d and then do addition here. But that's not going to give me the next month because February doesn't have thirty days. It's actually off by two days. So how would you do that? Well, there are special functions to help in situations like this. If I start typing a formula here and then I look at Date & Time functions I'll see all these great functions that work with Dates & Times. So let's look at the EDATE function. The EDATE function is what we want right here. It will do a calculation using a month off-set. So I can start with this date here and say I want 1 month off-set from it. You could also use the Date Diff function to get the difference between the two dates that will give you something better than adding or subtracting. If I do subtraction. I take this date times this date then I'm going to get that it's 4w. I can format that here using custom units and see it's 28d difference. But what I really want to get is the number of months that is different. Well, I can't divide by 30 or 31. I don't know how many days are going to be in the month as these values change. But I can, instead of using regular subtraction, use Date Diff. I can take the start date, the first one.Take the end date here and then choose a calculation method. In this case I'm going to choose Months. Now I'm going to get that it's one month different. 
So look through all of the different functions that handle Dates & Times here. See what could be useful to you. Read the descriptions. Look at the examples. Try some out for yourself in a sample table. There are also a bunch of functions that have to do with duration. Most of these have to do with converting a duration. Most of these have to do with converting a duration into a specific value. Like say the exact number of minutes and giving you that as a numerical value rather than a special duration value.
A quick look at formats here. Formats for dates are pretty straight forward. You see the different date formats here. The different times formats here. You could, of course, always go to Create Custom Format if you want. Durations are a little trickier. We're less familiar with those. The basic styles are to use either just a letter or a word like Weeks there. But you could also select Custom Units and select the unit you want. For instance if I said I don't want days then it's going to show me only hours and minutes. So I get 77h as opposed to 3d 5h. Also if you go to Automatic Units you can select just the number and then it's going to put colons between things. It doesn't make as much sense when you've got things like days. But it makes more sense when you've got just hours, minutes, seconds. So that shows you hours and minutes. That's something we're used to seeing. If you switch to that format you'll see the actual value shows you that format there at the bottom. So now it's easier, maybe, to type things like for instance I could do 9 hours 15 minutes and 45 seconds like that. 9:15:45. It understands that is what it is. I can switch over to the regular style like that 9h 15m 45s to test to see exactly what it is. 
So I hope you found this useful. Thanks for watching.

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

Gary Rosenzweig

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?

Gary Rosenzweig

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.

Gary Rosenzweig

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.

Gary Rosenzweig

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.

Gary Rosenzweig

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.

Gary Rosenzweig

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?

Gary Rosenzweig

4 months ago

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

Calculations With Time and Duration Values In Numbers

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

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

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Connect with MacMost

MacMost Sections

Information