Convert Time Duration To Decimal Hours In Mac Numbers

Hi, this is Gary with MacMost.com. Let me show you how to add up Time Durations in hours in Mac Numbers. 

Now say you want to keep track of the number of hours you worked doing something like a specific project so you're keeping the time on a specific spreadsheet. But you need to calculate the number of hours you worked each day. This can be little confusing because there are two different types of time values in Spreadsheets. One is specific time. Like an actual time right now like date, hour, minute second. Another type of time value is Duration, like the number of hours, minutes, seconds that have gone by. Of course what you want to do is have a Start Time and an End Time that have real time values. Then the number of hours worked as a duration. But you just don't want the duration. You want the actual number of hours. Maybe even rounded to the nearest quarter hour. So it takes a little bit of work to get that value. 

Let's start by taking a look at a spreadsheet that can serve as an example. So here we have dates in the first column. We have a Start Time and an End Time. So like the last one here you have the date and you may record, say, the start time as 10:00 a.m. and maybe the end time as 2:10 p.m. like that. So you have those three values here and you want to compute the duration for each one. Now this can be fairly simple if your data is clean. By that I mean if you look at these time values here. There is just the time. There's no date. But there is a date. If I select any one of these values every date value is a full value down to the second. So, for instance, even if I just typed 7/8 here to get this date of July 8th, if I select it and look at the value below I'll get the value below but there is a time there of midnight. If I look at this value here you can see it says July 8th, 9:50 a.m. This one says 5:30 p.m., which is no problem. If I were to take the end value and subtract it from the start value I get the 8th as the duration. I'm  going to use equals here and I'm going to click on the end time then minus the start time. Fairly simple and Return gives me 7 hours and 40 minutes. Perfect. If I take this and I double click on the column letter there it selects all of these and now I can paste it in. I get 7 hours and 40 minutes, 6 hours and 40 minutes. But wait a minute. Look at this one. One day is 5 hours and 40 minutes. Why is it that? Well, the start time was entered on that date. You can see it says July 9th. But the end time was not. When you finished work that day instead of recording the time you waited until the next day to do it and now you get the next day's date, the date you were using there. 5:15 p.m. But it took it as July 10th 5:15 p.m. 

Now you could just fix this by double clicking on it and changing the date. But if you don't want to go through all of that and always recording the date and the time for each one of these, you just want to make it really simple, we can do better by doing the calculations but ignoring the date. It is a little bit more complex than you would think. Let me show you how.

You're going to use a function called Time. When you look up Time in the Functions Help we can see Time takes hours, minutes, and seconds. We can also see we've got functions hours, minutes, and seconds that extract those values from any time value. So, we'll go and use Hours, Minutes, and Seconds in each one of these. For Hours, I'll do hours then parenthesis and then the hour for the start time. Then for the Minute I'll do minutes and then parenthesis and minute for the start time, and then for Second and the second from the start time. I have those three values, the hour, the minute, the second taken from the cell and placed as the three parameters in time. The result is I get the same time. It looks the same. But what it is, is it is actually the time without the date. Or more accurately the date is set to 1904 January 1st. So if I take the same formula, I'll just Copy here and Paste here, I'll get the values from here. I moved over one when I pasted so all of the references in it moved over one as well and now I get these two values but instead of being on separate dates it is on the same date. So I can do this cell minus this cell and it will correctly give me 5 hours and 40 minutes. 

So now I've got a good duration. How do I convert that to actual hours, like decimal hours. Not hours and minutes. Well you can do that using a function called DUR2hours. You look that up. You see it just takes a duration which is just what this value is. It will convert it to decimal hours. So like that. Now I get 5.67 hours. So that's good. That's what I want. 

Except now I also want to round it to the nearest quarter hour. Or better yet round it up to the quarter hour. So I'm going to go back into this formula here and instead of just converting it to that I'm also going to roundup. But if I roundup just this value and, you have to look up roundup. Always look up the function before you use it the first time, you can see it takes digits as the second parameter. So I need to say, oh I don't want any decimal digits. I want it to be rounded to the nearest integer. So when I do that you can see it rounded it up to 6. I want it rounded up to quarter hours. So to do that I want to convert duration to hours to quarter hours by multiplying by 4. So, like instead of 3 hours I'll get 12 quarter hours. Right. So now this rounds up to 23 quarter hours. To go back to hours I simply divide by 4, like this, and now I get the next highest quarter hour as the result here. 

So I'm doing it now in three different steps but I could easily combine this all into one. All I need to do is reproduce each part of this into one formula. Let's start by taking this cell here and pasting it there. I'm just going to grab the Start Time but doing it by using the Hour, Minute, Second function inside of the Time Function. I'm going to Select All, Copy it, and I'm going to subtract it from itself so it is always going to be zero. But the idea is I want to subtract the End Time from the Start Time. So, I'm going to update each one of these values here, like that. I can quickly update that first one. So now it is going to take the hour, minute, second of End Time inside the Time Function and subtract that from the hour, minute, second from the Start Time. There we go. I get the total time. 

Now I'm going to convert that using DUR2hours, like that. Close that parenthesis there. I get the duration in hours like that. I'm going to round it up to an hour with no decimals there. So it does that. But I don't want to just roundup that value. I want to multiply by 4 so turning up to the nearest quarter. So 21 quarters. Then at the end I will divide by 4 again to now take me back down to quarter hours, like that. So I get that. If I were to copy this, double click here and paste it in, you can see I get all these values here. Clear this out. Now I have exactly what I want. 

If you find these videos valuable consider joining the more than 3000 others that support MacMost through Patreon. You get exclusive content, course discounts, and more. You can read about it at macmost.com/patreon. 

But that's a pretty long formula mostly because of the conversions using Time and hour, minutes, and seconds. We can simplify this a little bit using a different function instead of that one. We're going to use a function called Time Value. Time Value not only strips out the date of a time but converts it to a decimal where one represents one day. So the time of 4:25 p.m. comes at .68 or 68% into the day.  So, we can simply subtract to time of values, like these two here, to get the amount of the day that passes between these two. In this case 23% of the day has passed between the End Time and the Start Time. But we don't even need to use duration to hours to convert to hours. All we need to do now is to convert this to quarter hours. 

What we do first is to put this in parentheses so subtraction remains grouped together. Then we're going to multiply by 24. This gets us to the number of hours. But we want the number of quarter hours. That gets the number of quarter hours. Now what we're going to do is we're going to use roundup, just like before, with comma zero to roundup to the nearest quarter hour there and then we'll divide by 4 to get the hours back from quarter hours. We paste this in here and we get the same result with different function but it is a little bit easier to read this. You can see just subtracting both times but each time is wrapped in Time Value to get rid of the date and then multiplying by 24 to get to hours, multiplying by 4 to go to quarter hours, rounding up and then dividing by 4 to go from quarter hours back to hours to give you what you want. 

If I want to total them up to see how many hours I worked for the project I'm going to Control Click or just click on the little Context Button here next to the last row and add a Footer row below. Right here I'm going to press equals and then Sum of the entire row E like that. Now I get the total number of hours worked. I hope you found this useful. Thanks for watching.