How many hours did Johnny work? [Excel Homework]

Story of Johnny, Hard working atom splitter:

Meet Johnny, who lives by the intersection of lane 2345, and BAG street. Johnny (or little Jon as friends call him) is a hard working family guy. He works at local nuclear power plant in a neighborhood named Sheet3. Due to a recent re-organization at work place, Jon found himself reporting to an almost crazy boss named “Bill Lumbergh”.

Now, Lumbergh is not your everyday crazy boss, he is so much more than that. So, one day Lumbergh walks over to Jon’s desk and tells him, “Look Jon, we are having issues in the particle accelerator in the basement. It doesn’t seem to split atoms alright. So I need you to go there and split atoms manually. We got 12,789,000 atoms to be split before next weekend. So I am gonna go ahead and ask you to work on it. Ummkay?”

Just when Johnny thought of uttering a curse, Lumbergh came back and reminded, “Oh Johnny, remember to log how much time you are spending splitting atoms. I need you to tell me how many hours you worked for every million atoms. Make sure you follow the latest Timesheet report formats, or else…,”

Johnny muttered a couple of real ugly cuss words that are not blog worthy and went about his business of splitting atoms. He managed split only 11 million of them before the deadline given by Lumbergh. Those darned atoms!

Also, Johnny logged the time he spent for every million atoms using, well, excel. Like this:

(Download the data)

Your homework:

Johnny needs your help to figure out how many hours he worked in total (as well as for each million). He is already tired hunting a missing electron in the basement alleys. So don’t tell him to count manually. He wants to have an excel formula that tells him how many hours he worked given a start and end date in cells A1 and A2 respectively. Remember,

• Johnny never works after 6pm or before 9am
• Johnny never works on weekends.
• Lets say Johnny doesnt take any lunch breaks (he has developed a taste for those higgs bosons sandwich with positron milk shake).

So I am gonna go ahead and ask you to complete this homework before the weekend. When you got the correct formula, come back here and post it in comments. Ummkay?

More excel homework:

• Find out days overlapped between 2 dates
• Average of top 5 values
• More Home work | Formula tutorials & examples

PS: If you feel lost, that is because you have not seen office space. Go watch it.
PPS: if you still feel lost, that is because you do not know NETWORKDAYS. Go learn it.