Excel only store 2 types of data

Excel only capture data in 2 forms, numbers and text. Numbers are aligned to right and text is aligned to the left. But Excel captured dates as well? How did it do it? Well, dates in Excel are stored as numbers. They are numbered according to the number of days from 1 Jan 1900. So the number 1 refers to 1 Jan 1900, 2 refers to 2 Jan 1900, and so on. Excel makes use of the Format Cells function display the number as a date.

The Format Cell function is activated through the icon found in the Home Tab, right click on the cell, or Ctrl + 1, etc. It can be formatted into different date formats such as 1 Feb 2017, 1/2/2017, 2/1/2017, 20170102, etc. It can also display the numbers into different formats such as currency, zip codes (postal codes), time, fractions, percentage, scientific, etc.

When you download dates from the system and notice that some are aligned to the left and some to the right, it means that some dates are numbers and some dates are not. This happened because the date format is not consistent with your computer timing.

When you download dates from the system and notice that some are aligned to the left and some to the right, it means that some dates are numbers and some dates are not. This happened because the date format is not consistent with your computer timing.

computer date

If the computer time is in different format (e.g m/d/yy) from the date in the data set (d/m/yy), the computer will mistake the day in the data as month. Those dates more than 12 will not be recognized and are taken as text (align to the left). Those that get converted to dates are also wrong because the day and month are reversed. For proper conversion of dates to the right format, you can use the TEXT-TO-COLUMN function found in the Data Tab.




New! Comments

Have your say about what you just read! Leave me a comment in the box below.