Date System in Excel
An Excel date is simply a serial number.
The earliest date that Excel can understand is January 1, 1900. This date has serial number 1. January 2, 1900, has the serial number of 2; January 3, 1900, has the serial number of 3, and so on. The last date Excel can understand is December 31, 9999.
You can convert a date to a serial number and a serial number to a date. In an Excel cell, say in cell A1, I type date 1/1/1900, it is actually 1st January 1900. Press Enter. Excel will automatically understand that this is a date.
If I change the format to General, this is what happens:
You see the cell value is 1.
Let’s try another date, 1/31/1900. What should be the serial value of this date? 31, right?
To note about the year 1900, Excel has a bug. 1900 is not a leap year. But mistakenly it considers it as one.
Type 12/31/1900, and press Enter. What should be the serial number of this date? It should be 365, right? As 1900 is not a leap year.
This was never corrected due to compatibility issues between different versions.
Let’s hope you will not have to do accounts for the year 1900 or analyze that year’s data with Excel.
Your computer’s regional settings will define what date format will be accepted by Excel. I am using “U. S. English language” Date format. In this format by default, the Month name comes first, then the day and at the end, the year sits. For example, in US English Language format the date 3/1/1952 refers to March 1, 1952.
In the UK English Language Format by default the day comes first, then the month and at the end, the year sits. So in UK English language format, 3/1/1952 refers to January 3, 1952.
There are several ways in which you can enter Dates in Excel.
The first way is: You can use a slash to separate the month, day, and year in a date as 6/18/2013. It is Excel’s default short form of a date.
You can also enter with hyphens instead of slashes. When you press Enter it gets converted to slash notation again.
You can also enter the dates of the current year not mentioning the year. It will put the current year.
If you enter dates that do not conform with the date format, they will be treated as text
You already know that dates and times are regarded as numeric values in Excel. So dates and times are aligned right in the Excel cells. Text entries are left-aligned in the worksheet cells. This is the simplest way you can use to identify whether an entry is a numeric value or a text value.
If you make the column width narrower, at a point, the date will be converted to hashes. But a text will never be converted to hashes.
Here’s a look at different formats available. In the attached Excel, you can check what they were initially entered as and how Excel brought it down to slash notation.
Time to talk about time now!
Time System in Excel
In Excel Time is also a serial number. 12:00:00 AM is regarded as 0;
1:30:00 am is 1 hour and 30 minutes from 12:00:00.
So its serial number is 90 minutes/ 1440 minutes=0.0625.
1440 minutes being the total minutes in a day.
In the same way, 10:00:00 PM has the serial value 0.92 as 10:00:00 PM means it is 22 hours away from 12:00:00 Am, so the serial is 22/24=0.9166666.
Let’s enter a date with time in an Excel cell. I type June 18, 2013, 1:30, press Enter.
To see how the date is saved to Excel, just click the cell again and look at the formula bar. See how the date is saved in Excel.
This is the serial number:
Let’s try this date with another time entry. June 18, 2013 26:00:00. This date and time entry is interpreted as June 19, 2013, 2:00:00 AM as 26 hours means it has one full day and 2 hours. So the date is June 19, 2013, 2:00:00 AM.
So time’s up for Time discussion. Let’s meet again on another Date to talk more about Excel Excellence.
The following things you have to remember when you enter date and time into Excel cells.
- The first date Excel can understand is 1st January 1900 and its serial number is 1.
- Excel has a bug in its date system. 1900 is not a Leap Year, but Excel considers it as a Leap Year.
- Your computer’s regional settings define the date format accepted by Excel.
- If the date format is unknown to Excel, then the date will be saved as Text in Excel.
- How to auto populate date in Excel when cell is updated
- How to Use Excel EDATE Function
- Excel Formula to Calculate Hours Worked Minus Lunch