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.
- Excel first checks if an entry is a numerical value
- If it's not a numerical value, Excel aligns text on the left of a cell
- Excel interprets dates as values and aligns on the right of a cell
- Excel also automatically changes dates to the Date or Custom format
- You can change the output format if you wish
- Dates are stored as numbers, seen in the General format
- This number is the number of days between 1/1/1900 and the current date
- Excel interprets times as values and aligns on the right of a cell
- Excel also automatically changes dates to the Time or Custom format
- Times are stored as a fraction of 24 hour day, seen in the General format
CTRL + 1: Open format cells dialog box
SHIFT + →: Select adjacent cells
ALT + E, S, F: Paste formulas
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 type 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 first 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 could 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 changed the format of the cell in this case to custom, or in the previous cases to date. If we want to change this format we can simply select the cells, Ctrl 1, Alt C, and date, and then select the format that we'd like to use. Self formatting ensures our dates are much more readable. However, this is not the format in which dates are stored in Excel. To understand how dates are stored 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 begins counting from the first 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 first of January, 1900. So for the first of January, 2013, we can see there are 41,275 days between that and the first 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'll 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 AM and I'll enter 3:00 PM, with a 24-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 24-hour day. With 6:00 AM equal to a quarter or 0.25 and 3:00 PM 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 signup data for its users. We'll start by fixing the formatting of the first name and last name columns in the next lesson.