Thomas asks if there is a better way to calculate Hours Worked. Thomas' data is imported from a web application, as Text. Today, in Episode #1374, Mike "ExcelIsFun" Girvin and Bill "MrExcel" Jelen try alternate formulas to solve the problem.
Transcript of the video:
Hey welcome back it's another dueling Excel podcast.
I'm Bill Jelen form MrExcel will be joined by Mike Girvin from Excel Is Fun.
This is episode 73, Time Worked.
All right, today's question send in by Thomas.
Thomas is getting data from a web application where the times are coming in as text 0908a 0445p needs to figure out how many hours between the two those and Thomas already has a formula doing this look check that out.
He has a question though, is there an easier way.
Okay anytime I'm getting a time as text the very first thing I do is I use, =TIMEVALUE(, and throw that text in there and see if it works, lots of formats do that one doesn't.
Okay I like the fact that we have 0445 that leaning 0 that helps a lot because what I'm gonna do is I'm gonna take the left of C9 comma 4 that'll give me those digits need to format those digits to stick a colon in the middle.
So, that will be the TEXT function.
TEXT function of that whole thing 00\:00 the backslash colon backslash says hey, whatever the next character is just stick it in at that point between the digits.
So, there's the text there's the time value point 197 that is correct that's about 4:45 a.m.
I could change it to a time format, but I'm going to just keep working I know that ultimately I'm going to have this back as a decimal.
So, I'm going to leave it where it is.
I now need to get that P for p.m. and so, I'm going to add a space here in the the numeric format ampersand the right of our time C9 comma 1 ampersand in quotes M, didn't work.
That is to be inside the TIMEVALUE.
Here you go, .697.
All right, I feel like I'm the halfway home at this point.
So, I take all of those characters except for the equal sign control+C copy them to the clipboard go out to the end of the formula minus control+V to paste and now, instead of C9 I'm gonna use B9.
All right, 2 spots .317 All right, that is the decimal portion of a day I need to take that whole thing and multiply it by 24, times 24, .76 or 7.61 that's the same thing that Thomas's formula is getting.
Great Oh... Oh no we're not okay 12:57 to 12:43 they worked past midnight anytime you're working past midnight.
Now, we need to change this formula we're to take the MOD of that whole thing up to the 24 comma 1.
All right, MOD stands for I think it's modulo or modulus, take a number divide it by another number you get the integer portion and the remainder this gives you just the remainder.
So, we're throwing out the whole days getting just a remainder and there's the exact same answer that Thomas has.
All right, so is there an easier way to calculate the hours work?
I don't know if this is any easier or not let's take a look at the just number of characters in the formula.
So, here's my apostrophe, control+V 124 characters of mine 139 characters and Thomas's I don't think that's marginally easier not a whole lot easier.
Hey, Mike let's see if you have a better way to do that.
Mike: Thanks MrExcel, thanks Thomas to.
Now, totally awesome foreigners I love in particular that MOD function to deal with the fact that we have night shift sometime and both of these the MOD was used.
Now, MrExcel went ahead and counted the characters and his form is a little bit shorter let's also count the functions and I'm just going to count the functions of that that are used to coax a time out of one particular cell.
So, here we won't look at those for Thomas is 1, 2, 3, 4 so, 5.
Over here MrExcel is 1, 2, 3, 4.
All right and so, not only we looking for a less length in the formula shorter to type out, but also fewer functions to have to run.
All right, I'm gonna hide these columns here right click, Hide and then see if I can come up with some...
Now, basically I'm gonna steal most of the tricks they use, the TEXT function totally awesome way to go.
Now the value, we don't have the value.
So, we have to say hey, left, take the left 4 characters and then we're gonna format that.
Now, I love MrExcel's trick here 0 and then a backslash that says I love it.
Insert that which is exactly what time needs a colon 00, close parenthesis now, close double quote, close parenthesis.
Now I'm gonna put one less 0 because that it formatting will pick up 99 and 12.
So, that'll work just fine now, that I forgot a comma now that gives us the 908, but we need the AM or PM.
So, ampersand and then write, write of this comma 1 that'll always get the A or the P and then we simply ampersand join symbol and double quotes M.
Now, right there that is not a proper time value.
Now, notice it's aligned to the left that means it's text, but if you're gonna coax a number from a text string various ways to do that, that space has to be there.
So I'm gonna come back over and actually you could prove yourself that this that number would not cannot be coaxed into a list I don't know how.
If we add do any operation on text, that's actually a number it'll convert it back to a number.
So, I'm going to add 0 that's my any operation it's a value, but as soon as I put that space right here meaning time space and then a.m. or p.m. boom it'll work.
Now, I'm gonna take this we got the time value from there copy this, copy I'm gonna need this minus this.
So, I'm going to control+V minus, now, I had to make sure and put all of this in parentheses.
If I want to subtract these two because, that ampersand is calculated after any arithmetic operator like that.
So, you have to force it to be calculated first by putting in parenthesis.
Now, I'm gonna change this the later time minus the earlier time.
All right and so, that will work.
Now, I want to copy this down there's the problem with night shift.
Now, what we really want is one plus this right because our actual time for working is 0.499 some, some, some.
So, let's see how the MOD function works.
MOD, MOD takes a number and divides it by something and gives you the remainder.
So, I'm gonna say hey, there's the number and the divisor or the denominator is one.
Is that totally awesome that's just unbelievable, but why does that work.
Microsoft help actually does work sometimes, when you're looking at function and here's a hot screen tip.
If you click on this, it will open up help and if you read through help, there's a little algorithm here and there it is too small to see right here.
So, I'll scoop it out copy and then paste it, escape, right up here and there's our algorithm that's how it's calculated.
So, the end that's that part right there - d* and then our time divided by 1.
Let's just go ahead and see what this divided by 1 is.
Well, of course it's just going to give us the value, right all the way down, but what does the INT function do.
The INT function is kind of the magic part of this algorithm in function takes something and gives adjust the integer part, but guess what it always goes down.
So, from these 2 numbers going down it gets to 00, but here when it goes down what does it go it goes to negative 1.
So, here's INT we will get a 00 and a negative one.
Now, if we our D is 1 so, we simply 1 times and it gives us the same thing 0, 0, -1 and guess what when you take this that's the N and minus it.
Well, what's a minus, minus when it gets down here it's actually 1.
So, it takes this plus 1 and that's how we get which we really wanted to, that's what we want it and so, that's how the MOD function works.
Now, let's put it all together right here, here's that formula I'm going to say MOD.
Yeah, that's right do I have no that's not right MOD I need one more parenthesis right there.
There's the screen tip number divisor.
So, I'm gonna come here comma 1 and then times 24 double click and send it down and so there we have our times.
If I right click Un-hide, so, there you go mostly we got to see how that MOD function does it's magic I think maybe a few.
Oh, it's three functions.
So, one, two, three, three functions.
All right, throw back over to MrExcel.
Bill: Beautiful Mike, great explanation of MOD from Excel help of all places.
See you next time for another netcast form MrExcel and Excel Is Fun.
I'm Bill Jelen form MrExcel will be joined by Mike Girvin from Excel Is Fun.
This is episode 73, Time Worked.
All right, today's question send in by Thomas.
Thomas is getting data from a web application where the times are coming in as text 0908a 0445p needs to figure out how many hours between the two those and Thomas already has a formula doing this look check that out.
He has a question though, is there an easier way.
Okay anytime I'm getting a time as text the very first thing I do is I use, =TIMEVALUE(, and throw that text in there and see if it works, lots of formats do that one doesn't.
Okay I like the fact that we have 0445 that leaning 0 that helps a lot because what I'm gonna do is I'm gonna take the left of C9 comma 4 that'll give me those digits need to format those digits to stick a colon in the middle.
So, that will be the TEXT function.
TEXT function of that whole thing 00\:00 the backslash colon backslash says hey, whatever the next character is just stick it in at that point between the digits.
So, there's the text there's the time value point 197 that is correct that's about 4:45 a.m.
I could change it to a time format, but I'm going to just keep working I know that ultimately I'm going to have this back as a decimal.
So, I'm going to leave it where it is.
I now need to get that P for p.m. and so, I'm going to add a space here in the the numeric format ampersand the right of our time C9 comma 1 ampersand in quotes M, didn't work.
That is to be inside the TIMEVALUE.
Here you go, .697.
All right, I feel like I'm the halfway home at this point.
So, I take all of those characters except for the equal sign control+C copy them to the clipboard go out to the end of the formula minus control+V to paste and now, instead of C9 I'm gonna use B9.
All right, 2 spots .317 All right, that is the decimal portion of a day I need to take that whole thing and multiply it by 24, times 24, .76 or 7.61 that's the same thing that Thomas's formula is getting.
Great Oh... Oh no we're not okay 12:57 to 12:43 they worked past midnight anytime you're working past midnight.
Now, we need to change this formula we're to take the MOD of that whole thing up to the 24 comma 1.
All right, MOD stands for I think it's modulo or modulus, take a number divide it by another number you get the integer portion and the remainder this gives you just the remainder.
So, we're throwing out the whole days getting just a remainder and there's the exact same answer that Thomas has.
All right, so is there an easier way to calculate the hours work?
I don't know if this is any easier or not let's take a look at the just number of characters in the formula.
So, here's my apostrophe, control+V 124 characters of mine 139 characters and Thomas's I don't think that's marginally easier not a whole lot easier.
Hey, Mike let's see if you have a better way to do that.
Mike: Thanks MrExcel, thanks Thomas to.
Now, totally awesome foreigners I love in particular that MOD function to deal with the fact that we have night shift sometime and both of these the MOD was used.
Now, MrExcel went ahead and counted the characters and his form is a little bit shorter let's also count the functions and I'm just going to count the functions of that that are used to coax a time out of one particular cell.
So, here we won't look at those for Thomas is 1, 2, 3, 4 so, 5.
Over here MrExcel is 1, 2, 3, 4.
All right and so, not only we looking for a less length in the formula shorter to type out, but also fewer functions to have to run.
All right, I'm gonna hide these columns here right click, Hide and then see if I can come up with some...
Now, basically I'm gonna steal most of the tricks they use, the TEXT function totally awesome way to go.
Now the value, we don't have the value.
So, we have to say hey, left, take the left 4 characters and then we're gonna format that.
Now, I love MrExcel's trick here 0 and then a backslash that says I love it.
Insert that which is exactly what time needs a colon 00, close parenthesis now, close double quote, close parenthesis.
Now I'm gonna put one less 0 because that it formatting will pick up 99 and 12.
So, that'll work just fine now, that I forgot a comma now that gives us the 908, but we need the AM or PM.
So, ampersand and then write, write of this comma 1 that'll always get the A or the P and then we simply ampersand join symbol and double quotes M.
Now, right there that is not a proper time value.
Now, notice it's aligned to the left that means it's text, but if you're gonna coax a number from a text string various ways to do that, that space has to be there.
So I'm gonna come back over and actually you could prove yourself that this that number would not cannot be coaxed into a list I don't know how.
If we add do any operation on text, that's actually a number it'll convert it back to a number.
So, I'm going to add 0 that's my any operation it's a value, but as soon as I put that space right here meaning time space and then a.m. or p.m. boom it'll work.
Now, I'm gonna take this we got the time value from there copy this, copy I'm gonna need this minus this.
So, I'm going to control+V minus, now, I had to make sure and put all of this in parentheses.
If I want to subtract these two because, that ampersand is calculated after any arithmetic operator like that.
So, you have to force it to be calculated first by putting in parenthesis.
Now, I'm gonna change this the later time minus the earlier time.
All right and so, that will work.
Now, I want to copy this down there's the problem with night shift.
Now, what we really want is one plus this right because our actual time for working is 0.499 some, some, some.
So, let's see how the MOD function works.
MOD, MOD takes a number and divides it by something and gives you the remainder.
So, I'm gonna say hey, there's the number and the divisor or the denominator is one.
Is that totally awesome that's just unbelievable, but why does that work.
Microsoft help actually does work sometimes, when you're looking at function and here's a hot screen tip.
If you click on this, it will open up help and if you read through help, there's a little algorithm here and there it is too small to see right here.
So, I'll scoop it out copy and then paste it, escape, right up here and there's our algorithm that's how it's calculated.
So, the end that's that part right there - d* and then our time divided by 1.
Let's just go ahead and see what this divided by 1 is.
Well, of course it's just going to give us the value, right all the way down, but what does the INT function do.
The INT function is kind of the magic part of this algorithm in function takes something and gives adjust the integer part, but guess what it always goes down.
So, from these 2 numbers going down it gets to 00, but here when it goes down what does it go it goes to negative 1.
So, here's INT we will get a 00 and a negative one.
Now, if we our D is 1 so, we simply 1 times and it gives us the same thing 0, 0, -1 and guess what when you take this that's the N and minus it.
Well, what's a minus, minus when it gets down here it's actually 1.
So, it takes this plus 1 and that's how we get which we really wanted to, that's what we want it and so, that's how the MOD function works.
Now, let's put it all together right here, here's that formula I'm going to say MOD.
Yeah, that's right do I have no that's not right MOD I need one more parenthesis right there.
There's the screen tip number divisor.
So, I'm gonna come here comma 1 and then times 24 double click and send it down and so there we have our times.
If I right click Un-hide, so, there you go mostly we got to see how that MOD function does it's magic I think maybe a few.
Oh, it's three functions.
So, one, two, three, three functions.
All right, throw back over to MrExcel.
Bill: Beautiful Mike, great explanation of MOD from Excel help of all places.
See you next time for another netcast form MrExcel and Excel Is Fun.
