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:
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.
17 Responses to “Custom Number Formats – Colors”
You are right, Chandoo. I was playing with the colour numbers last week and some of them don't appear different from each other. Others are totally different from yours.
@Duncan
Each version of Excel, post 2003, renders colors slightly differently
Different language versions may also have different default color palettes
Hello in french
excel 2010
colo1 = couleur1 = black
[couleur1]; [couleur2]; etc..
@Hui, thank you very much again for this great post.
However - under Excel 2007, Hungarian version your solution does not work with color names. I've tried both English and Hungarian names, but drops an error message "not valid formats"
Do you have any idea how to solve this issue?
thanks in advance
@Andras
Without a Hungarian version of Excel 2003 I don't think I can assist
Have you tried using the colour numbers? I couldn't get the names to work (despite using an english version of excel). but it did work with the numbers though. I left out the "u" and was easily able to produce burgundy using [color9]
Here a possible solution: find an English version of Excel, write there the formats using English names, then open the file in the Hungarian version and see the translation.
In Excel 2007 I can't get the colour names to work e.g Sea Green but the numbers do e.g color3 - colour3 does not work so I must bow to the country that has stolen my language (ha ha!)
Hey chandoo, nice Tip!
Wouldn't be easier just apply some conditional formatting for negative numbers and another for positive numbers? Or there's some cases that you can't do that?
Unfortunately the TEXT function doesn't color the cell as number formatting does.
Hi Hui,
Great post Sir, love the new way of formatting with color numbers.
I am using 2007, and it leads me to the last color number 56.
Thanks Hui.
[…] explains how to set up custom number formats with a wide array of […]
Thanks Hui - works a treat!
Thank you, very helpful.
Trying to figure out if it is possible to apply color only to a part of the cell?
E.g. I have a value formatted as Accounting with a currency symbol.
Those I find somewhat distracting though necessary. If I could make them less obtrusive by coloring them gray while the number would stay black, that would be great. Tried tinkering with the format string, but didn't get the desired result. Single color for complete cell value works, but coloring just part of it could not be achieved. Maybe somebody managed that?
Exactly what I was looking for - thank you!
colour in the Australian doesn't work - we have to go American and no problem.
I always thought is was 56 colours notice you have 57. Cool.
thanks
Analir Pisani
Customised Microsoft Office Training Specialist
Sydney - Australia
http://www.azsolutions.com.au
Thank You!