About Date Formatting

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.

24^th 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 1^st 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, 2^nd Jan 1900-12:00:00 AM is 2….and so on. In that order 41722 comes to 24^th 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.