Shaun has a beautiful spreadsheet to calculate staffing levels and expense.He needs to multiply shift length by the number of staff and the answer that he is getting is . . . immaterial to the task at hand. With a few changes in the Formula and Cell Formats, Bill shows us in Episode #1468 how to get the real resulting Total Hours and Cost.
...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!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...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!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1468, Adding Time.
Hey, welcome back to the MrExcel netcast.
I’m Bill Jelen.
Sean sent me this beautiful spreadsheet.
Hundreds and hundreds of rows, but I am able to put the whole problem here in one line.
He had a whole bunch of people that worked the same job.
They started from 7 to 3, means they had an eight hour shift.
So, that's pretty cool; C3 minus B3.
They were making nine and a quarter per hour and we want to figure out those 21 people times eight hours what that's going to be.
So, we do 21 times 8 a.m. and we get this.
What is that?
All right and as I looked at Sean’s spreadsheet, he had actually gone into the number tab and under time, he had used, I think, this one, 1:30 p.m. click OK and the same thing over here.
I'll press the F4 key.
Still it doesn't help.
What you have to do if you want to get the total number of hours, is you have to go into that format cells and look for the one called 37:30:55.
That's the one with more than 24 hours.
So, we choose that one and now, we actually get the correct answer of 168 hours.
But hey, this looks really stupid tracking the number of seconds that's somebody worked.
So, let's go back in, Ctrl+1, after we've chosen that, go down to custom and we're going to get rid of the seconds part.
So, just get rid of the colon SS.
And then that ampersand, @ sign or semicolon @ sign is saying if it's not numeric, just treat it as text.
I'm just going to leave that off because I know it's going to be the numeric because it's a formula.
So, we have 168 hours.
Now, we actually want to multiply the 9.25 x 168.
Then we have to do equal 9.25 x 160 hours x 24.
We'll still test here plus 168 x 9.25, see if that works.
Yes, that's good.
The reason is because an hour is actually stored as one twenty-fourth of a day, by multiplying it by 24, we convert to actual hours.
So, a couple of tricks there, the big one is using that 37:30:55, weird time format which ends up putting square brackets around the h and then we can get rid of the colon SS.
Completely unintuitive.
I don't know anyone was ever supposed to figure that out.
Think about it.
You know doing a time sheet eight hours Monday through Friday adds up to 40 and it comes out to 16.
It's just one of the bizarre things about Excel, but hopefully with this, Sean will be able to look at that gorgeous spreadsheet work.
I want to thank you for stopping by.
I want to thank Sean for sending that question in.
We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1468, Adding Time.
Hey, welcome back to the MrExcel netcast.
I’m Bill Jelen.
Sean sent me this beautiful spreadsheet.
Hundreds and hundreds of rows, but I am able to put the whole problem here in one line.
He had a whole bunch of people that worked the same job.
They started from 7 to 3, means they had an eight hour shift.
So, that's pretty cool; C3 minus B3.
They were making nine and a quarter per hour and we want to figure out those 21 people times eight hours what that's going to be.
So, we do 21 times 8 a.m. and we get this.
What is that?
All right and as I looked at Sean’s spreadsheet, he had actually gone into the number tab and under time, he had used, I think, this one, 1:30 p.m. click OK and the same thing over here.
I'll press the F4 key.
Still it doesn't help.
What you have to do if you want to get the total number of hours, is you have to go into that format cells and look for the one called 37:30:55.
That's the one with more than 24 hours.
So, we choose that one and now, we actually get the correct answer of 168 hours.
But hey, this looks really stupid tracking the number of seconds that's somebody worked.
So, let's go back in, Ctrl+1, after we've chosen that, go down to custom and we're going to get rid of the seconds part.
So, just get rid of the colon SS.
And then that ampersand, @ sign or semicolon @ sign is saying if it's not numeric, just treat it as text.
I'm just going to leave that off because I know it's going to be the numeric because it's a formula.
So, we have 168 hours.
Now, we actually want to multiply the 9.25 x 168.
Then we have to do equal 9.25 x 160 hours x 24.
We'll still test here plus 168 x 9.25, see if that works.
Yes, that's good.
The reason is because an hour is actually stored as one twenty-fourth of a day, by multiplying it by 24, we convert to actual hours.
So, a couple of tricks there, the big one is using that 37:30:55, weird time format which ends up putting square brackets around the h and then we can get rid of the colon SS.
Completely unintuitive.
I don't know anyone was ever supposed to figure that out.
Think about it.
You know doing a time sheet eight hours Monday through Friday adds up to 40 and it comes out to 16.
It's just one of the bizarre things about Excel, but hopefully with this, Sean will be able to look at that gorgeous spreadsheet work.
I want to thank you for stopping by.
I want to thank Sean for sending that question in.
We'll see you next time for another netcast from MrExcel.
