There are several data formats defined already. It this Excel tutorial you will learn how to deafine a custom one.
Number format creation
For you to set own custom number format in Excel, you first need a number in the cell that looks like this:
Note: The number can of course, be anything numerical.
Mark all the numbers.
Right click on the marked area (1), and choose format cells (2).
Click custom and set the customized number under type.
Most commonly used codes
Some of the most commonly used codes include:
- #: Represents a digit placeholder. If a number has more digits than there are digit placeholders, the extra digits will be displayed. If a number has fewer digits than there are digit placeholders, the extra placeholders will be left blank.
- 0: Represents a digit placeholder, but if a number has fewer digits than there are digit placeholders, the extra placeholders will be filled with zeros.
- . (period): Specifies the position of the decimal point in the number format.
- , (comma): Specifies the position of the thousands separator in the number format.
- %: Specifies that the number should be displayed as a percentage.
- $: Specifies that the number should be displayed as a currency value, with the appropriate currency symbol.
When defining a custom number format, you can combine these codes to create a format that meets your specific needs.
Custom formant examples
For example, to display a number with a comma as the thousands separator and two decimal places, you could use the format "#,##0.00". To display a percentage with one decimal place, you could use the format "0.0%".
To display 123456.78 as 123456.8 use ####.#
.123 as 0.1 use 0.#
5.25 as 5 1/4 use # ???/???
Note: You can even format colors. Just write [color]
How to Format Date and Time?
How to Easily Format Date and Time in Excel Format of date and time can be tough, but it will easily be performed together with me, follow me as I format date and time.
Date formatting
Click on a cell (1), type in =DATE(2014(year);10(month);05(day)) (2), and press enter.
Time formatting
Click on another empty cell (1), and type in =TIME(hour;minutes;seconds) (2), and finally press enter.
Right click on the time, and choose format cells.
Click on date (1), and choose desired format (2), before clicking ok.
Repeat previous step, but this time choose time (1), the type you want (2), and then press ok (3).
With that knowledge you will be able to use date duration calculator without any problems.