Excel stores dates as values; each date, starting at January 1, 1900, has a numerical value. This makes it easy to use dates in formulas and functions.
- [Instructor] Excel's ability to handle date and time information is a powerful tool. And there are any number of situations where it's going to come in handy. For example, in this worksheet called dates in our chapter one file, column B has higher dates. And from that information, years of service are being calculated in column C. Over the date in columns J, K, and L, we've got dates in column L. Eventually, we might want to figure out which day of the week these sales were on. And there are certainly other situations where we want to calculate days elapsed between different dates.
Calculate due dates in other situations. To understand how Excel deals with dates, when you enter a simple date, and most often within the United States, you type something like this; April fifth, four slash five slash 17. And Excel will typically display it this way. What you've really entered there is a value. The fact that it's right aligned perhaps is a clue as to that concept. Remember in Excel, any time you type a number, press enter, it ends up on the right hand side of the cell. Now, behind the scenes, what's happening here is every date has a separate value.
Each day is different from the day before, the day after by one. The starting point in the Excel scheme is January first 1900. Rarely would you ever enter this. If you were typing it, you would probably type it this way. And, you have entered the value one. Now rarely, but occasionally, you might want to know what a date's number is. You can certainly click on a date. And on the home tab in the number grip, click on the comma button for example. It simply has the value of one. And January second 1900 has the value of two.
And so on, and so on, and so on. Occasionally, you might get data from other sources. And you might have a column with the word date at the top. And sometimes you'll just see a bunch of numbers. If they happen to be in the 40, 42,000 range, probably they really are dates, but they haven't been formatted properly. More about that later. So at different times, if you're curious about numbers that should be dates, you might want to simply convert them into a date display. Now, occasionally you might run into another issue. And it's a bit of a problem.
What if you've got data that's coming from Excel on the Mac? There the date system begins January second 1904. So, here's what you might have to do and it gets confusing at times. I'm going to go to the file tab in the ribbon down the left hand side and choose options. And amongst the choices on the left hand side, we see advanced, and then in this large list here, scrolling toward the bottom, we come to a category here.
When calculating this workbook, use the 1904 date system. Now before I do this, I'm going to move over so we can see some of those dates in column L. If I switch to the 1904 date system right now, soon as I click ok, watch those dates in column L. Right now they're mostly 2015, 2016. And now suddenly they're 2019, 2020. We've changed those dates. Earlier, if you recall, I had typed in here four slash five slash 17, meaning 2017. Now we have this.
Now, you've got to be careful when you're working with this. There are no hard and fast rules, but occasionally you run into that idea. Now, as we work with other data, as long as you keep in mind the idea that every date is a value. It helps to understand how Excel works with dates. And in subsequent movies, you'll see many many different techniques for working with different data entries in Excel. And for the moment, I'll go back to the file tab in the ribbon. Choose options. And once again, advanced down the left hand side.
And scrolling near the bottom of the list here. Under when calculating this workbook. I'll turn off this 1904 date system. Again, that's only going to be a problem if you work with data coming from Excel in the Mac. And moving back and forth sometimes, you want to be very careful when you're working with dates.
LinkedIn Learning (Lynda.com) is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.
- How Excel records and stores dates
- Using standard date/time entries and acceptable alternatives
- Formatting dates and times
- Creating standard date/time formats
- Working with dates and times in functions
- Calculating with dates and times
- Using dates/times with Excel commands