When you enter 2/2 in a cell,
Excel thinks smart and will format the cell value as 2-Feb. As I mentioned in
an earlier post, Excel treats any cell entry in only 2 categories i.e. a number
and a non-number. Date is just a number
that is in disguise as date. Let’s see in detail.
We will enter couple of dates in
few cells like below.
Once you are done entering the
dates, notice the Number Format box.
It will show Date as applied format
for these cells. Select all these dates, and change the number format to General. (Click Home tab > Number group
or press Ctrl+1). These dates will
now be changed as in below picture.
24th Mar 2014 became
number 41722 and so on. What is this?
1 January 1900
Excel uses 1900 date system by
default. That means, it thinks the world has started on 1st Jan
1900. This date is considered as 1 and each day after this is increased by 1.
So 1 Jan 1900-12:00:00 AM is 1, 2nd Jan 1900-12:00:00 AM is 2….and so
on. In that order 41722 comes to 24th Mar 2014.
Excel also has another date system
called 1904. We will see about that sometime later but not here.
Alright, so one day is
represented by 1. Did you get the doubt, what about hours, minutes &
seconds, how to show them? Simple! To show fractions of a day, 1 is to be
divided by respective fraction. For example, if you want an hour, divide 1 by
24, result denotes an hour. You can do math for other fractions. See below for the
fractions, rounded off to 6 decimal points.
To facilitates calculations, 0
(zero) is treated as 0 Jan 1900-12:00:00 AM, though date 0 Jan is not logical.
So when you add dates or subtract dates, what’s happening really is addition or
subtraction of 2 numbers behind the scenes. Now you know how you can
add/subtract dates from each other.
Now let’s see how few numbers will
be displayed as dates.
I used a custom format “dddd, d
mmmm yyyy hh:mm:ss AM/PM” to show above example. (I will write about this
format shortly).
There are various built-in date and
time formats available in Excel. Check out different formats by following the
below sequence.
To Set Date Format
Press Ctrl+1 > Date tab
> Select appropriate option under Type
There are around 13 different
types available for Date format.
Choose the one you fancy and your date in that cell will show just like it.
To Set Time Format
Press Ctrl+1 > Time tab
> Select appropriate option under Type
There are 3 types available for Time formatting.
Human needs are unlimited, right?
If you are not satisfied with built-in formats, Excel also facilitates you to
create your own. We will see about that in next post.
