Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In Excel 2010 Power Shortcuts, Excel expert Dennis Taylor shares tips and shortcuts to vastly increase efficiency and get the full power out of Excel 2010. There are tips for working with the Ribbon and Quick Access toolbar, navigating workbooks and selecting cells, rapid data entry and editing, working with formulas, formatting data, working with charts, sorting data, and much more. Exercise files accompany the course.
Excel has any number of capabilities and functions related to dates and times. And handling date and time information in Excel, although occasionally tricky, is powerful and generally easy to use. But some sometimes we are in the situations that require little bit of conversion. The data here in column L is an accurate reflection of what's going on in a particular organization, where they are tracking hours worked on a project. But this information here, 3.5, it means three and a half hours and this is four and a quarter hours, really should have been entered differently.
This should have been entered as 3:30, three and a half hours, four and a quarter hours, 4:15. Now if you get a ton of these you think, "I guess we got to retype them." No, there is a faster way around this. We simply in an adjacent column highlight these cells, write a single formula here and then press Ctrl+Enter = this value divided by 24, 24 hours a day. And one of our top 10 tips was to press Ctrl+ Enter since we have got all these highlighted.
The answer we see here at first is not going to look as if it's correct, but it is. And we'll simply format this. Right- click, Format Cells, and use the Time format of 13:30, not any of the ones with AM/PM. There we go. Here are the answers we'd actually like to see here. Now we could copy these to themselves or simply copy them into column L with a right drag, and again using one of our top 10 tips of copy/paste values. Drag any edge with the right mouse button on top of the other data.
Let go of the mouse. Copy Here as Values Only. And once again we will have to format this. Right-click. Format Cells, Time to 13:30. We are all set. We can now get rid of this data. Now if we want to tabulate the total here, put the active cell here, double-click AutoSum. We are all "oops, we are not all set are we?" We know that's not 45 minutes. Strangely enough if we highlight this data here we do see the correct total at the bottom of the screen. Its 48 hours and 45 minutes.
What's not so obvious is the fact that when you are adding minutes and hours, if they exceed 24 you get the remainder of that. And if they exceed 48 you get the remainder over 48 and so on. What we need here is a special format. Right- click, Format Cells, and it's not an obvious choice. As we click these various choices here, we keep an eye on the sample. It's not until we click the one that has 37 in it that this rings true. And even here it's a little bit off because it's showing us seconds as well.
We don't really need this. Jump over into Custom. Clean up this display here by eliminating the seconds and all that information to the right. And this is going to give us based on that sample a display that we want that makes good sense. Any time you are trying to tabulate hours that go above hours that go above 24 you will need that special format.
Find answers to the most frequently asked questions about Excel 2010 Power Shortcuts.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.