Learn Excel 2010 - "Added Times Won't Calculate": Podcasts #1468

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Nov 10, 2011.
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!
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top