Sign in or start a free trial to avail of this feature.
1. How Excel Deals with Text, Dates, and Times
It is vital that users understand how Excel displays and stores text, date and time values. Find out all you need to know in this lesson.
How Excel Deals with Text (00:14)
When you enter data in a cell, Excel checks if it can be interpreted as a numeric value. For a text string, like a name, this won’t be possible. Excel will align text like this to the left of a cell.
How Excel Deals with Dates (00:46)
When you enter a date in a cell, Excel recognizes it as a value, and aligns it to the right of the cell. When Excel recognizes a date, it will assign a Date format to the cell. Excel recognizes many different date formats, including short formats (e.g. 1/1/2013) and long formats (e.g. 1 January 2013. You can change how dates are displayed using the Format Cells window, which you can access by pressing Ctrl + 1.
Excel stores dates as numerical values. Specifically, dates are stored as the number of days from 1 January 1900. For example, 1 January 2013 is stored as the number 41275, because there are that many days between 1 January 2013 and 1 January 1900. Storing dates as numbers lets us perform numerical calculations on dates. However, we rarely display data in this format, instead using one of the Date formats.
How Excel Deals with Times (03:09)
When you enter time data in a cell, Excel stores it in a Time or Custom format. Excel recognizes times in many different formats, including 12 and 24 hour clocks. Like dates, times are stored as a number in Excel. Specifically, a time is stored as a fraction of a 24 hour day. For example 6:00 am is stored as 0.25. 3:00 pm, or 15:00, is stored as 0.625.
In this, our first lesson on text, dates, and times, we're going to spend a couple of minutes understanding how Excel interprets and stores these different data types. Let's start off by looking at text. So I'll take my name...
and press enter. And when I do this, excel automatically aligns the text on the left hand side of the cell. So what's going on here? Well whenever we enter some data into a cell, Excel first checks if the entry can be interpreted as a numerical value. Because the text entered is simply my name, Excel does not interpret this as a numerical value. And so the entry is considered a text string. And aligned on the left hand side of the cell. Now let's see what happens if I enter a date. So I'll write the 1st of Jan 2013 and then press enter.
This time around, Excel reads the entry and determines that my input is a value, and as a result, aligns the entry on the right hand side of the cell. Excel has also determined that my entry is a date and automatically assigned a date format for the cell. I can actually enter dates, in a number of different formats and each time Excel will determine that they are values and of type date. So, for example, I can separate the day, the month, and the year by slashes.
Or I could write out the long form of the date.
In each case, Excel interprets and stores what I've written as numerical values. And this property of Excel allows us to perform calculations on dates. Excel also changes the format of the cell, in this case to custom, or in previous cases, to date. If we want to change this format, we can simply select the cells, control + 1, Alt + C, and date.
And then select the format that we'd like to use. Cell formatting insures our dates are much more readable. However, this is not the format in which dates are store in Excel. To understand how dates are store by Excel, I'll copy and paste these cells in the adjacent column. And then I'll change their format to general.
This provides us with three numbers that might seem a little random at first. To store date values, Excel beings counting from the 1st of January 1900 and assigns this day with the value of one. All other dates are stored as a number that's simply the number of days between that entered date and the 1st of January 1900. So, for the 1st of January 2013, we can see there are 41,275 days between that and the 1st of January 1900. It is this method of data storage that allows us to perform numerical calculations on dates in Excel. However, this format is not very intuitive. So we almost always keep our dates, in a date format. To store time values, Excel performs a similar task. Again, I'll enter some time values on the left. So, I'll enter 6:00 a.m.
And I'll enter 3:00 p.m. with a twenty four hour clock. With a colon between the hours and the minutes. And again, in each case, Excel interprets this as a numerical value, and applies a custom format to each cell. If I copy and paste these values into the adjacent column, we can see the numerical values associated with times. As you can probably tell from these values, Excel stores times as a fraction of a twenty four hour day. With 6:00 a.m. equal to a quarter, or 0.25. And 3:00 p.m.
stored as 0.625.
Again, because Excel stores time in this way, we can perform numerical calculations on time values. As with dates, we'll keep time in an easily understood custom format, almost all of the time, instead of the general format. In the first part of this course, we'll focus on how to chop, change, combine and edit text strings. In the second half of the course, we'll focus on times and dates. Exploring the techniques and functions that can be used to help you gain insights from your time series data. For the remaining lessons in this course, we'll use a sample data set from a fictional online company, which stores all the sign up data for it's users. We'll start by fixing the formatting of the First Name and Last Name columns in the next lesson.