Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
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.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 87385 Viewers
80 Video lessons · 136393 Viewers
59 Video lessons · 55043 Viewers
52 Video lessons · 68910 Viewers
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.