Join Dennis Taylor for an in-depth discussion in this video Using special formats for times over 24 hours, part of Excel for Mac 2011 Tips and Tricks.
- View Offline
- On the worksheet called Auto Fill, let's scroll to the right so that we can see columns L and M. Recording Hours Worked as 3.5, although it's workable, isn't really the best thing to have done here. If you work with times of day in Excel, there can be some confusion at times. Sometimes 3:30 means three hours and 30 minutes. At other times it means 3:30 a.m. So let's convert these numbers into times, divide them by 24. Now, the unit of measure as you work with dates and times in Excel is one equals an entire day.
So if we divide this by 24, equal M2 divided by 24, the number we see may not be that revealing. I'll double-click and copy this down the column. But let's now format this by way of command one. It'll take us to the Format Cells dialogue box. We'll choose Time. And choose the entry here without the AM PM and say 13:30, that's what we want. And now I think you can see what we've got here. Instead of 3.5, 3:30.
Now, let me reselect these. I'll hold down the shift key and double-click the bottom edge and simply copy this data on top of the old data using the right mouse button. Right mouse button, drag it to this data, let go of the right mouse button, Copy Here as Values Only. And once again, this happens now. The reason that happened is I didn't copy the format. I copied the values. So once again, command one and we'll press Time and 13:30, there we go. And we'll get rid of the data in column N. Now, I wanna know how many hours it's going to be per project.
First I'll just do a grand total at the bottom here. Click the bottom, let's get a total. Command shift T is a fast way, activates AutoSum. We press return. Well, that couldn't be the right answer. How did that happen? I'm going to highlight these numbers first then look at the bottom of the screen. 277 hours. Well, I haven't looked through the numbers that close. Yeah, that looks like it could be right. But that certainly isn't 277 hours. What's going on here? Maybe try this with a smaller set of data.
What if I were to just take these numbers here? Well, that's gonna be over 24 hours. But I'm just going to copy those to the right. And with the option key held down, I'll simply drag these to the right and let go of the mouse first. What's the total of these? Bottom of the screen says 35. I'll put a total in right here. Command shift T and return. What a minute. 35, and we get 11. What's going on here? Oddly enough, when you're adding times of day or hours of the day, as we are here, anytime we go for 24, we get the remainder there.
Or if we went over 48, we get the remainder over 48 and 72 and 96 and so on. That doesn't really help. What we really want to do here is to come up with the correct answer. And what we do is easy. But on the other hand, why do we have to do it? And that I can't answer. I'm gonna press command one. We go to Format Cells and go to the Time category. And as we look through the list here, one thing of note, if you do click an option, you do get a preview up above. So I'm gonna click the option for 37.
Maybe that's process of elimination. That looks like it's got the right answer, 35. Would you have ever guessed that? If the format has the 37 in it, this means we can handle totals that go above 24 hours. Now, one other note here, this now has hours, minutes, seconds. If we don't want to see those, we can jump over to Custom. It's still being displayed. And in the display here, let's only leave in display hours and minutes. And once again, a little bit of obscurity here.
A small letter H here between brackets means what? It handles hours over 24, as if we should all know that. Click OK, and now we've got a sensible answer. And our answer at the bottom of the screen right here, I could have copied the format, but I'll jump right back in again, command one, go to Time, pick the one with the 37, and there it is. This time it didn't take off the seconds. But 277 hours is correct. And highlighting the numbers does show us that total at the bottom, provided in our status bar we've got the Sum showing.
Remember, you can click the drop arrow and make other choices if you wish. So anytime you're working with hours, if you want to tabulate them, make sure the format is correct. Otherwise, you'll get answers that are just way, way off. At least they're way off and you know that. But at times they could be off just a little bit. You wouldn't necessarily catch it. You've got to change the format when you're working with times.
Learn the top shortcuts, find out how to most efficiently navigate and control the display, and discover the best ways to select, enter, and format data. The course also includes ways to leverage drag-and-drop features, shortcuts for formulas and operations, data management efficiency techniques, guidelines for working with charts efficiently, and a selection of quick tips.
- Converting formulas to values with a simple drag
- Entering today's date or time instantly
- Accessing Ribbon commands from the keyboard
- Creating split screens fast
- Navigating and zooming quickly
- Entering data more efficiently
- Performing calculations without formulas
- Applying formatting with keyboard shortcuts
- Quickly cleaning up extra spaces and deleting duplicate entries