Some data downloaded from Carnegie Learning is causing problems in Excel. Although the data looks like HH:MM times, they all add to zero. Here are the steps needed to convert the text times to real times.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1927.
Why Won’t These Times Sum Up?
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Burdette.
Burdette downloaded this data from Carnegie Learning.
Looks like it's some hours and minutes that people spent on various sections, he wants to total that time up and average it.
But the TOTAL formula is not working, we're getting zeros there.
Very annoying and the AVERAGE – same thing.
It's the right formula, but it's giving us the wrong results.
So even though these look like times and it's formatted as General, clearly something is wrong there.
My bulletproof way to tell this, is Ctrl+` (the little accent key underneath the Esc).
See, those times, if they were real times, they would have shown a fractional portion of a day.
But they're not.
They're showing still, so it must be text, because it keeps showing us the same thing.
So I'll press that key again to go back.
Alright, so let's see different ways to solve this.
One way: select the text, it looks like time, do DATA, Text to Columns.
We don't have to answer any questions, just click Finish.
And you see that now we're starting to get real answers over here.
But only from that one column.
We'd have to go back and do this for all of the columns.
Faster way: Alt, D, E, F – is the shortcut key for DATA, Text to Columns.
Finish, unfortunately have to do this one column at a time.
Alt, D, E, F.
For those of you screaming out there, that we should use Copy, Paste Special: sure, yeah, we could put a one on the clipboard like that, Ctrl+ C and then select the whole range and do Alt, E, S, and we want to multiply, so M, and also values, so Alt+E, S, M, V, press Enter.
See, but it screws up the formatting.
Yeah, faster way to go, but now we have to go back and choose a custom number format.
So, you know, and this one that's built in here, the Time, it's a horrible one, because it's not at all like what we want it to look like.
If I wanted to look like those, I guess Ctrl+1.
And I don't even think there's one built in, because that doesn't have the leading zero.
So, the custom number format is: HH:MM, like that, click OK.
And now everything comes back.
Right, now at this point, we're 92% on the way there, right.
We've converted all of these times to… all these texts that looks like time to real time.
And how could I tell?
Well, first of all, the numbers are working.
Also, when I do Ctrl+`, I'm seeing decimal portions of the day in all columns.
That tells me that that is working.
Press that key again.
But the thing that still might be a problem is, if anyone took more than 24 hours to finish this test, this time format over here is not going to give us the right result.
And certainly this total at the bottom does not appear to be the right result.
So we have to use a special custom number format there.
And actually, you know what, I'm just going to use it in all of the cells.
So I want to press Ctrl+1 and we go to Custom.
All right, and what was the number we used before?
HH:MM, like that, but here's the big difference: we have to put the HH in square brackets [ HH ].
That is the really obscure way to tell Excel: hey, even if we have, you know, two days and three hours, I want you to show that as 51 hours.
Don't, don't show me, you know, don't cut off the day’s portion of this and just show me the hours in excess of full days.
And sure enough, yeah, right there, that 24:07 and that 123:32 – those are the real answers there, right?
So hey, this should have been simple Carnegie Learning census data, but unfortunately, you know, they’d sent the data down, somehow, however they exported it, it was kind of evil.
Sent it down as text, I managed to format the cells as general, so it all looked right, but none of the formulas were working.
All right, hey, I want to thank Burdette for sending that question in and I want to thank you for stopping by, I’ll see you next time for another netcast – MrExcel.
Learn Excel from MrExcel podcast, episode 1927.
Why Won’t These Times Sum Up?
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Burdette.
Burdette downloaded this data from Carnegie Learning.
Looks like it's some hours and minutes that people spent on various sections, he wants to total that time up and average it.
But the TOTAL formula is not working, we're getting zeros there.
Very annoying and the AVERAGE – same thing.
It's the right formula, but it's giving us the wrong results.
So even though these look like times and it's formatted as General, clearly something is wrong there.
My bulletproof way to tell this, is Ctrl+` (the little accent key underneath the Esc).
See, those times, if they were real times, they would have shown a fractional portion of a day.
But they're not.
They're showing still, so it must be text, because it keeps showing us the same thing.
So I'll press that key again to go back.
Alright, so let's see different ways to solve this.
One way: select the text, it looks like time, do DATA, Text to Columns.
We don't have to answer any questions, just click Finish.
And you see that now we're starting to get real answers over here.
But only from that one column.
We'd have to go back and do this for all of the columns.
Faster way: Alt, D, E, F – is the shortcut key for DATA, Text to Columns.
Finish, unfortunately have to do this one column at a time.
Alt, D, E, F.
For those of you screaming out there, that we should use Copy, Paste Special: sure, yeah, we could put a one on the clipboard like that, Ctrl+ C and then select the whole range and do Alt, E, S, and we want to multiply, so M, and also values, so Alt+E, S, M, V, press Enter.
See, but it screws up the formatting.
Yeah, faster way to go, but now we have to go back and choose a custom number format.
So, you know, and this one that's built in here, the Time, it's a horrible one, because it's not at all like what we want it to look like.
If I wanted to look like those, I guess Ctrl+1.
And I don't even think there's one built in, because that doesn't have the leading zero.
So, the custom number format is: HH:MM, like that, click OK.
And now everything comes back.
Right, now at this point, we're 92% on the way there, right.
We've converted all of these times to… all these texts that looks like time to real time.
And how could I tell?
Well, first of all, the numbers are working.
Also, when I do Ctrl+`, I'm seeing decimal portions of the day in all columns.
That tells me that that is working.
Press that key again.
But the thing that still might be a problem is, if anyone took more than 24 hours to finish this test, this time format over here is not going to give us the right result.
And certainly this total at the bottom does not appear to be the right result.
So we have to use a special custom number format there.
And actually, you know what, I'm just going to use it in all of the cells.
So I want to press Ctrl+1 and we go to Custom.
All right, and what was the number we used before?
HH:MM, like that, but here's the big difference: we have to put the HH in square brackets [ HH ].
That is the really obscure way to tell Excel: hey, even if we have, you know, two days and three hours, I want you to show that as 51 hours.
Don't, don't show me, you know, don't cut off the day’s portion of this and just show me the hours in excess of full days.
And sure enough, yeah, right there, that 24:07 and that 123:32 – those are the real answers there, right?
So hey, this should have been simple Carnegie Learning census data, but unfortunately, you know, they’d sent the data down, somehow, however they exported it, it was kind of evil.
Sent it down as text, I managed to format the cells as general, so it all looked right, but none of the formulas were working.
All right, hey, I want to thank Burdette for sending that question in and I want to thank you for stopping by, I’ll see you next time for another netcast – MrExcel.
