MacMost Now 747: Using Time and Duration Values In Numbers

You can represent specific times and time durations in cells in iWork Numbers. You can format the cells to display the times and dates in a variety of ways. You can perform calculations on both and get intelligent results, such as the difference between two times.

You can also watch this video at YouTube (but with ads).

Video Transcript: Hi, this is Gary with MacMost Now. In today's episode let's take a look at using time values in Numbers.
So, Numbers of course can use regular numbers or amounts in cells and you can perform functions on them and create formulas that give you results. But you can also do the same thing with time and it will correctly display time in a certain format and also do calculations using minutes and seconds. Let's take a look. 
So here I am in Numbers. Let's not create anything fancy here. Let's just go ahead and put some sort of number values in here. Say I want to measure something in seconds so I want to do 120 seconds here. Now of course it displays as 120. How do I actually format that so that it knows it is a time. Well, you can bring up the Inspector of course. In Inspector you can go to the cell format and there you can change it to a Duration. This will display it how you want it using the different units you want. So in this case hours, minutes, and seconds. Now I can actually tweak that to minutes, seconds, milliseconds or days, hours, minutes. I can even stretch it to include all of those. So let's actually put it down at minutes and seconds. Then I can furthermore go and figure out the format for it. Whether I want it to be that type of format, or this, or just spelled out completely. So let's do it like that.
Now of course I want to change this because I notice that when I entered 120 it took it to mean 120 hours. So hours is the base unit here. But I can actually enter in something using the formula that is there. So if I do 120 and then put "s" for seconds it will recognize it and you can see it will even convert it there to 2 minutes 0 seconds. Now if I wanted to format it so I just saw seconds, for instance, I can shrink that down and there I get 120 seconds. 
If I did it like this I could do some cool stuff like say 121 seconds and it will know it. I can also type in 4 minutes and 5 seconds and it will know it. And if I switch formats it will follow suit. So you can see it does it like that. So I can type in say 2 minutes 4 seconds and it converts it properly. Likewise if I had the format set the other way like and I type in 02:07 you can see it converts it and uses it correctly. 
So how about performing calculations on this. Well I can create another thing here. Now watch what happens in this cell it is formatted automatically. So it is like regular numbers here. But if I type something that looks like a time, this is what I get. It automatically figures out that I wanted time and now changes the format of that cell so that's what the automatic formatting does. This one is set to be duration. This one is set to be automatic here. So I can do the same thing if I typed like 03:25. In this case it figures out the time which we will get to in a second.
Let's perform a calculation first on this. Say I want to calculate the difference between these two. I can just do it as I did before. I am just going to do = this minus that and it will put it correctly in the correct format. It realizes that I am subtracting one time from another and the result will put as time.
Now what happens if I use time rather than duration. So for instance let's do twenty-four hours and say I want to do 1900 (7:00 PM) and I want to do 2130 (9:30 PM). What happens if I try to subtract these. So I will do this minus this and it figures out that I am subtracting two points in time and will give me a duration as the result. 
Likewise I can set the time like say 8:00 and give a duration like 3 seconds. Then I could do a formula for that time minus this duration and it figures it out. In this case of course it is rounding to minutes. I can actually select here and see the cell format is set to automatic which is date and time and I can actually set it to seconds there.
Now I can go further than that and actually use a date. So for instance let me say January 9, 2013. Then let me do October 5, 2012. It figures out that these are date formats and I could go and subtract them to find out the difference between the dates. It tells me 96 days.
So there are some of the basics how to use time and duration inside of Numbers. You see it works pretty naturally. You type in the format that you want. Perform calculations and the results are as you expect. So now you can add things like for instance a column for the time you check into work and the time you check out of work and then have the formula calculate how long you were at work. Then you could total those up at the bottom of that column just by performing a simple sum and it will total up all the durations.
So I hope you found this useful and I hope it gives you some ideas of what you can do with Numbers, times and durations. 
This is Gary with MacMost now.

Comments: 4 Responses to “MacMost Now 747: Using Time and Duration Values In Numbers”

Nilesh Parmar

12 years ago

Wow I didn't realise it can do that, great tut

Chuck

Interesting. But I'd be interested in seeing if you can add a time stamp to entries. Maybe using that IF formula with checkboxes. When you arrive at work and check the 'IN' box it shows the time in the field beside it. When you check the "OUT" box it also displays the time and then calculates the time worked, or whatever. Is that possible? Or is there an easier way to do it?

And, BTW Gary- you do a great job with this. Your library of videos is extremely useful. I hope the sponsors are catching on to you. Chuck

Gary Rosenzweig

I don't think so. Formulas always update to show "live" data, so having it change the data after an action, but then stop changing it after that... not something I think I've seen. Maybe a database app is more appropriate for this sort of thing.

John Pollard

Very impressive tutorials. Well done.

Comments Closed.

MacMost Now 747: Using Time and Duration Values In Numbers

Comments: 4 Responses to “MacMost Now 747: Using Time and Duration Values In Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information