Today, Leslie has a schedule for the week and she needs to total the hours. The solution appears to be complicated to arrive at the proper result, so... a A Macro? No... With dashes, no dashes things become a little more involved... but Bill arrives at a 7 Function solution, and then starts to Duel himself in a 'Solo Dueling Excel'. Follow along with Episode #1690 as MrExcel works out a solution to Totaling these hours.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1690 - Total: 8:30 to 5:00!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Leslie, Leslie has entered the schedule here for the week, 830-4, 430-1030, OFF, 5-10, must know how to total it.
Right, it sounds so simple, how do we total it?
Initially I'm like "No, you can't do that, or maybe can do that with VBA." But I started to think about it.
Well, you know, it's complicated, but we can do it.
If there's no dash, OFF and VAC, then the hours are 0, if there is a dash, we have to break the cell into the start time and the end time.
So we can split that, if the start is greater than 2 digits, then we know that there's minutes, 830-1030.
So that's if we have 3 or 4 digits, we have to deal with those minutes, take the minutes, separate them out, divide by 60, and then add to the hours.
And then we also have this one, once we get this, let's say we convert this to 4.5 and 10.5, but there was a day where, as they worked from maybe 1030-5, where the end time is less than- oh here this one, 830-4, perfect.
So once we get the start and end, if the end is earlier than the start we have to add 12 to the end.
Finally, do the subtraction and then add the whole thing up.
You know, we're in trouble if it took a 01:26 just to explain what we're going to do.
So first we have a "Dash", =FIND the dash in the cell, and that's going to tell us the position of the dashes, which is going to be very, very helpful.
Ah, see those value errors though, so if there is no dash we're going to get an error which we can actually use to our advantage.
Alright, so the "Start, as text", well we want the =LEFT of this cell, comma this number, because the dash is in the 4th cell -1.
Alright, so that gives us 8:30, but we're going to get values over here.
But what I had said was, if there's no dash, we're going to use a 0, so we're going to say =IFERROR, if that gives us a error, then just assume the start time is zero.
There, so we handled the dash, and then I need to get the "End, as text".
So that's =MID of this entry up here, comma, we want to start at the dash +1, and then we want to- how many characters do we want to get?
We want to get the length of B2, minus where the dash is, and that should get us, in that case, 4.
Now of course, we're going to need the IFERROR here as well, so let me just put that in before I copy across, if there is no dash then the end time is 0.
Alright, so now we have start 830, and we need to figure out if we have minutes or not.
So let's do "Start, as Hours", alright, so =IF, if the length of this answer >2, then we know that we have minutes.
We want to take the RIGHT of this answer, ,2 , divide it by 60 to convert those minutes back to hours.
And we want to add that to the hours, so that's the LEFT of B4, comma, the length of B4 -2.
Alright, so that's if we have 830, 3 digits or 4 digits.
If we just have 1 or 2 digits, then we just want the answer which will be B4+0, and that will convert it to hours.
So if that works, that's going to be amazing.
Alright, "Start, as Hours", there we go, good.
Alright, "End, as Hours", well this one's actually going to be simple, it's just going to copy the formula from above, Ctrl+C, Ctrl+V.
Alright, and then we need to adjust the end, =IF the start is greater than the end, then 12, otherwise 0 plus the end.
And then finally, we have Hours Today" is equal to end, minus start, Ctrl+V, copy across.
See that, I already have my =SUM function up here, =SUM to sum everything up.
So let's just try it, so instead of being off on Wednesday, let's see you went in for 2 hours, you work from 11-1, of course I have to enter ' before that, or convert these to texts, 11-1, should go from 1830-2030.
Hey alright, looks like it is working.
Let's see if we can do something really bizarre, like 1115-130.
No colon no colon no colon, 1115-130.
20.75!
OK, good!
See now, that's really important that you make sure not to enter the colon there.
You know, all these formulas are set up assuming that you're going to follow the same rules that you initially did.
Now let's convert these to text to make life a little bit easier.
There's some times that will look like a date like, you know, 5-1 will look like a date.
So explicitly choose text there, that way even if I enter 5-11, that will not get converted to a date.
Well there you go, it sounded like a really, really simple problem, in the end we ended up using how many functions?
SUM, FIND, IFERROR, LEFT, MID, LEN, and RIGHT - 7 different functions!
And, in my case, what, 35-36 different formulas to answer that one simple question.
Of course, Mike Girvin ExcelIsFun will probably pop in here with some array formula that can solve this in one cell, but that's Mike, you know, he's the author of the best-selling array formula book on the planet.
So there you have it.
Leslie, thanks for sending that question in.
No, NO, wait wait wait, don't close it yet, there's another way to do this!
So this is kind of a Dueling Excel podcast with MrExcel VS.
MrExcel.
While I was waiting for the other part to render I just said "Well you know, I wonder if we can shorten those 35 formulas down." And we can!
We can combine all the formulas in that Monday column, into a single big, long formula, which I'll put on the call out there.
And then you could copy those going across, that way we can actually, realistically, have one row for every week, you know, all the weeks, and have a total here.
If you don't want to see these columns out here, you can right-click and hide, and it would actually, realistically, work.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1690 - Total: 8:30 to 5:00!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Leslie, Leslie has entered the schedule here for the week, 830-4, 430-1030, OFF, 5-10, must know how to total it.
Right, it sounds so simple, how do we total it?
Initially I'm like "No, you can't do that, or maybe can do that with VBA." But I started to think about it.
Well, you know, it's complicated, but we can do it.
If there's no dash, OFF and VAC, then the hours are 0, if there is a dash, we have to break the cell into the start time and the end time.
So we can split that, if the start is greater than 2 digits, then we know that there's minutes, 830-1030.
So that's if we have 3 or 4 digits, we have to deal with those minutes, take the minutes, separate them out, divide by 60, and then add to the hours.
And then we also have this one, once we get this, let's say we convert this to 4.5 and 10.5, but there was a day where, as they worked from maybe 1030-5, where the end time is less than- oh here this one, 830-4, perfect.
So once we get the start and end, if the end is earlier than the start we have to add 12 to the end.
Finally, do the subtraction and then add the whole thing up.
You know, we're in trouble if it took a 01:26 just to explain what we're going to do.
So first we have a "Dash", =FIND the dash in the cell, and that's going to tell us the position of the dashes, which is going to be very, very helpful.
Ah, see those value errors though, so if there is no dash we're going to get an error which we can actually use to our advantage.
Alright, so the "Start, as text", well we want the =LEFT of this cell, comma this number, because the dash is in the 4th cell -1.
Alright, so that gives us 8:30, but we're going to get values over here.
But what I had said was, if there's no dash, we're going to use a 0, so we're going to say =IFERROR, if that gives us a error, then just assume the start time is zero.
There, so we handled the dash, and then I need to get the "End, as text".
So that's =MID of this entry up here, comma, we want to start at the dash +1, and then we want to- how many characters do we want to get?
We want to get the length of B2, minus where the dash is, and that should get us, in that case, 4.
Now of course, we're going to need the IFERROR here as well, so let me just put that in before I copy across, if there is no dash then the end time is 0.
Alright, so now we have start 830, and we need to figure out if we have minutes or not.
So let's do "Start, as Hours", alright, so =IF, if the length of this answer >2, then we know that we have minutes.
We want to take the RIGHT of this answer, ,2 , divide it by 60 to convert those minutes back to hours.
And we want to add that to the hours, so that's the LEFT of B4, comma, the length of B4 -2.
Alright, so that's if we have 830, 3 digits or 4 digits.
If we just have 1 or 2 digits, then we just want the answer which will be B4+0, and that will convert it to hours.
So if that works, that's going to be amazing.
Alright, "Start, as Hours", there we go, good.
Alright, "End, as Hours", well this one's actually going to be simple, it's just going to copy the formula from above, Ctrl+C, Ctrl+V.
Alright, and then we need to adjust the end, =IF the start is greater than the end, then 12, otherwise 0 plus the end.
And then finally, we have Hours Today" is equal to end, minus start, Ctrl+V, copy across.
See that, I already have my =SUM function up here, =SUM to sum everything up.
So let's just try it, so instead of being off on Wednesday, let's see you went in for 2 hours, you work from 11-1, of course I have to enter ' before that, or convert these to texts, 11-1, should go from 1830-2030.
Hey alright, looks like it is working.
Let's see if we can do something really bizarre, like 1115-130.
No colon no colon no colon, 1115-130.
20.75!
OK, good!
See now, that's really important that you make sure not to enter the colon there.
You know, all these formulas are set up assuming that you're going to follow the same rules that you initially did.
Now let's convert these to text to make life a little bit easier.
There's some times that will look like a date like, you know, 5-1 will look like a date.
So explicitly choose text there, that way even if I enter 5-11, that will not get converted to a date.
Well there you go, it sounded like a really, really simple problem, in the end we ended up using how many functions?
SUM, FIND, IFERROR, LEFT, MID, LEN, and RIGHT - 7 different functions!
And, in my case, what, 35-36 different formulas to answer that one simple question.
Of course, Mike Girvin ExcelIsFun will probably pop in here with some array formula that can solve this in one cell, but that's Mike, you know, he's the author of the best-selling array formula book on the planet.
So there you have it.
Leslie, thanks for sending that question in.
No, NO, wait wait wait, don't close it yet, there's another way to do this!
So this is kind of a Dueling Excel podcast with MrExcel VS.
MrExcel.
While I was waiting for the other part to render I just said "Well you know, I wonder if we can shorten those 35 formulas down." And we can!
We can combine all the formulas in that Monday column, into a single big, long formula, which I'll put on the call out there.
And then you could copy those going across, that way we can actually, realistically, have one row for every week, you know, all the weeks, and have a total here.
If you don't want to see these columns out here, you can right-click and hide, and it would actually, realistically, work.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
