how to understand number custom format

[lezawang]

Hi
I want to understand these codes under number custom formatting. For example what does #,##0;-#,##0 mean or what does #,##0.00;[Red]-#,##0.00 mean.

Is there any website/video explain step by steps what these codes mean and how you can created your own.
I have seen some video/articles about this but no one really explain for example why they put 2 ## and what they put 0 in ##0 and what does #,##0 mean.. and why not ###,##0 instead.

I am sure there is a syntax or format to use these symbols to create rule or number formatting. Any help would be very much appreciate it. Thank you.

 

[mole999]

in essence
#,##0.00;[Red]-#,##0.00
the # will suppress unnecessary zeros, the semi colon separates a positive or negative return, in this case it it would present with a minus sign and also in RED

 

[AhoyNC]

These links may be of help.

https://support.office.com/en-us/ar...r-format-78f2a361-936b-4c03-8772-09fab54be7f4
http://www.chartrecipes.com/article-custom-formatting.html
https://www.myonlinetraininghub.com/excel-custom-number-format-guide

 

[lezawang]

Thank you all for the help. I really appreciate it. I am reading now https://support.office.com/en-us/ar...r-format-78f2a361-936b-4c03-8772-09fab54be7f4
I tried to do a custome format with this code "people" @
basically i want to see if excel will add word "people" into the cell i select to format.
here what I have noticed:
if i have 2 cells, one has number = 10 and one has text= hello and then apply that formatting then the cell which has number, the number 10 move to the left of the cell but stay same 10 and the cell which has the text the text will be "people hello". After that, if I type any number or text inside the cell which has 10, the number or the text will be "people10"

My questions
1)Why the cell which has number, the "hello" word did not inserted while the cell which has the text, hello word inserted right away.
2) why the word "hello" inserted at the begging? can I change that to make it at the end, i mean after the text?

Thank you

 

[lezawang]

Another question please.
In that article i says the following

[TABLE="class: banded flipColors, width: 768"]
<tbody style="box-sizing: border-box;">[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4F4F4]#F4F4F4[/URL] "]
[TD][FONT=&quot]0 (zero)[/FONT]
[/TD]
[TD][FONT=&quot]This digit placeholder displays insignificant zeros if a number has fewer digits than there are zeros in the format. For example, if you type 8.9, and you want it to be displayed as 8.90, use the format #.00.
[/FONT]
[/TD]
[/TR]
</tbody>[/TABLE]

but instead of #.00 i can write it like this 0.00 and both give same answer
What I do not understand when # is used and when 0 is used? thanks

 

[Rick Rothstein]

Another question please.
In that article i says the following

[TABLE="class: banded flipColors, width: 768"]
<tbody style="box-sizing: border-box;">[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4F4F4]#F4F4F4[/URL] "]
[TD][FONT="]0 (zero)[/FONT][/COLOR]
[/TD]
[TD][COLOR=#2F2F2F][FONT="]This digit placeholder displays insignificant zeros if a number has fewer digits than there are zeros in the format. For example, if you type 8.9, and you want it to be displayed as 8.90, use the format #.00.
[/FONT]
[/TD]
[/TR]
</tbody>[/TABLE]

but instead of #.00 i can write it like this 0.00 and both give same answer
What I do not understand when # is used and when 0 is used? thanks

I am not sure why they used 8.9 in their example as what they are talking about only applies to values less than one... the question revolves around whether to show the leading, insignificant zero in front of the decimal point or not...

for 0.8...

#.00 displays .80

whereas...

0.00 displays 0.80

 

[lezawang]

Thank you very much. I got it now.

 

[lezawang]

Hi all
In the same article above, the write said this formula


[TABLE="class: banded flipColors, width: 768"]
<tbody>[TR]
[TD]time[/TD]
[TD]4 AM[/TD]
[TD="class: x-hidden-focus"]h AM/PM[/TD]
[/TR]
</tbody>[/TABLE]

So i used that one. and whatever i type in the cell, it will be converted to 12 am ? and when i click on the cell to see what is the content, I found this:

01/01/1900 12:00:00 AM

Why is that?

Also in the same article, it says that

[TABLE="class: banded flipColors, width: 768"]
<tbody>[TR]
[TD]Elapsed time (hours and minutes)[/TD]
[TD]1:02[/TD]
[TD][h]:mm[/TD]
[/TR]
</tbody>[/TABLE]
What does elapsed time mean? i do not see any use for that formatting thank you

 

[shg]

What does elapsed time mean? i do not see any use for that formatting thank you

How long does it take you to drive to work?

 

[FDibbins]

...So i used that one. and whatever i type in the cell, it will be converted to 12 am ? and when i click on the cell to see what is the content, I found this:

01/01/1900 12:00:00 AM

Why is that?

You probably entered 1.5 into that cell

excel sees dates as days passed from 0/0/1900, to if you enter 1.5 in a cell formatted like yours, that I what it will show