Have you ever found that the number, date or time format that you want to use isn’t available? These can be customised to provide exactly what you need. These options are found from the format cells option, selecting number, then custom.
Customised number formats can consist of up to 4 parts. Each part represents a different type of information. Some or all of these can be used as required. By looking at examples already created by default some idea of the structure required can be learnt. These preset options can be selected then modified or the structure of the format can be typed from scratch.
The structure of number formats is:
Positive number; negative number; zero, text
- Positive numbers are shown with a thousand separator and rounded to 2 decimal places
- Negative numbers will have the same format but in red and in brackets
- Zeros will show the word ZERO
- Text will be coloured blue
There are other options that can be applied if required.
These use differing numbers of d’s, m’s and y’s to determine how days months and years are shown.
d – day of the month as 1 figure if possible if not 2
dd – day of the month always as 2 figures
ddd – short version of the day of the week
dddd – long version of the day of the week
m – month number shown as 1 figure if possible if not 2
mm – month number always as 2 figures
mmm – short version of the month name
mmmm – long version of the month name
yy – year shown as 2 figures
yyyyy – year shown as 4 figures
If the date is 1st April 2018 then dddd dd m yy will change the format of the date to Monday 01 Apr 18
In a similar way to dates h’s, m’s and s’s can be used to format hours, minutes and seconds
h – hour using 1 figure if possible if not 2
hh – hour always as 2 figures
m – minute using 1 figure if possible if not 2
mm – minute always as 2 figures
s – second using 1 figure if possible if not 2
ss – second always as 2 figures
If AM/PM are added then the time shows as AM or PM or without this it shows as a 24 hour clock time
If the h’s are in square brackets they can go over 24. For example if you are trying to work out the number of hours between 2 times.
If the time was 8:25 in the afternoon then hh:mm AM/PM would show as 08:25 PM while hh:mm would show as 20:25.
If the time was 28:25 then hh