|
Tech forum
Custom formats in Excel
Excel has a large number of custom formatting options
which are extremely useful, yet quite unknown. These do not just change the
visible format, but also change the behaviour of data entered in Excel. This
section will describe many such useful formatting options. A must for any serious
user of Excel.
In Excel, when you type something in a cell, it requires to be shown automatically.
How is date and time data handled?
Excel stores date times as numbers. Any date is stored as the number of days
from a base date – which is 1st January 1990. This base date is stored
as a serial number 1. Future dates are stored as the appropriate serial number.
For example, 3rd Jan 90 would be stored as a serial no 3. Time is stored as
decimal fractions. As these are numbers you can of course perform calculations
like additions and subtraction. However, date data has some special needs.
Let us look at how date data is stored. Type a date, say 15 APR 2004 12:23 pm
. Excel understands that this looks like a date. So it internally applies the
Date format to the cell. Now select the cell and apply the General format. What
do you see? You see a long decimal number - 38092.51597. This is how Excel internally
stored this particular date and time.
- Applying date format changes the way data is handled
This is one of the most important things that you need to
know about Excel. The format that you apply determines the results of the data.
This is a fundamental thought. Let me illustrate this with an example.
Let us add the number 1 to the base date and the numeric representation of the
date.
Original Format Added Result
Value Value
15-Apr-2004 Date 1 16-Apr-2004
38092.51597 General 1 38093.51597
Since the format in the second case is General, it just adds the number 1. However,
in case of the date, it actually increases the date value by 1 day.
This proves that applying the format called date had some functional impact
on the way things work.
* Adding and subtracting dates
This is simple because the result is anyway expected to be a number. Consider
the example below. It simply shows that there are 14,612 days between the two
dates. This is fine. But is this what you wanted? How do we mentally understand
the difference between two dates? Not as a flat integer. Right? We understand
it as years, months, hours, etc.
B C D E
6 13-Apr-64 15-Apr-04 =c6-b6 14612
7
Now, if you wanted to know how many years and months does 14,612 really mean,
you will need to manually write formulas to divide it by 365 and then the remainder
by 30 and so on. This is too cumbersome. There must be a better way. Is it not?
We want to see this as ‘years:months:days’. How do I do that?
There is no function to do this. Simply put a custom format for the cell E6
which contains the number of days between the two dates. Choose Format –
Cells – Format tab.
Now choose custom from the list of formats available. Now type yy:mm as the
format and choose Ok. That’s it. The cell now shows years and months instead
of the number of days. Simple and intuitive.
Please note that we did not have to use any formulas here.
Just formatting. This is the power of formatting.
* Finding time difference between two date-time values
In the above example we subtracted two dates which were very far apart. Therefore,
we were interested in the difference of years or months. In many other cases,
it is important to find out the time difference to calculate the duration in
hours and minutes. This is useful for timesheets, project management or for
managing any time bound data.
How do we do this? Here is an example:
12-Mar-04 12-Mar-04 0.0715277780 1:43 hh:mm
00:30 02:13
The first two cells contain two different date time values. The third cell contains
a simple subtraction. This is a decimal value. Therefore, we can not find out
the duration in hours. Now if you apply the custom format hh:mm to the cell,
the time difference of 1 hour and 42 minutes is automatically shown.
Isn’t this great?
* Adding time values to a date
Consider that you want to add 4 hours and 50 minutes to a date time value. How
will you do this? If you add 4.50 as the value then it would be wrongly interpreted
by Excel and lead to wrong results. Consider the following example.
 12-Mar-04
12:00 AM
4.5
16-Mar-04 12:00 PM
Consider this data. The second cell is added to the first cell. What would you
have expected? The date should have remained same and the time should have become
4:30 AM. But here Excel added 4.5 days to the date, not to the time! How do
we inform Excel that the number we are entering is not just a decimal number
(which it uses to add to dates) but a ‘hours and minutes’ pair?
One option is to manually calculate all these time values (base 60) from decimal
values where base is 100. It will work but there is no need to use such a cumbersome
process.
Again the answer is simple — use custom formatting.
This time all you have to do is apply the custom format hh:mm to the second
cell. That’s it. The time will now be calculated as expected. The second
column shows the formatting that was applied to each of the three cells.
12-Mar-04 12:00 AM dd-mmm-yy hh:mm am/pm
04:50 hh:mm
12-Mar-04 04:50 AM dd-mmm-yy hh:mm am/pm
Converting month numbers to month names
This is another common requirement. Consider the data below.
Month Revenue
3 343
7 432
1 144
Now we want to replace the names of months in the month column. What do we do
normally? Either use a Vlookup function or use some other manually written code
to translate the number to text. Now of course we have formats.
It is so simple that you will curse yourself (in case you did not know this
feature). Just select the month numbers and apply a custom format called mmm.
Now the data will be converted appropriately.
Month Revenue
Mar 343
Jul 432
Nov 44
What do I do if I want the full name – i.e. ‘January’ instead
of ‘Jan’. For this purpose just add another m. Yes, I am talking sense.
Just change the custom format to mmmm. That’s it.
Summary
If you read this column regularly, you must be aware of the concept of ‘false
sense of knowledge’ that I have always talked about. These formatting features
are so easy and useful that you must be already feeling that you have fully
understood about Excel custom formatting. Unfortunately, the moment you think
you have learnt something, you stop learning further. Don’t let that happen
to you.
What we have seen now are few examples which were specific to date time data.
In next article I will cover additional nuances of Excel custom formatting features.
|
