Learn how Excel stores dates and times as numeric values. Author Jen McBee demonstrates how to apply formatting to cells to display the value in a date format, a time format, or date and time format.
- [Instructor] Did you know that dates and times are actually stored in Excel as serialized sequential numbers. It's only when we format the cell using date and time formats that the date, time, or date and time are displayed in something other than a numeric format. In the video, we'll talk about how dates and times are assigned that numeric value, and we're going to take a look at two functions you may encounter on the exam. The DATE function, which will serialize any date for you, and DATEDIF.
The DATEDIF function allows you to enter a start date and an end date, and Excel will tell you how many years, months or days have passed between those two dates. I have the 04_06 Serialize_Dates workbook open, so let's take a look. First I want to talk about the beginning of computer time. I mentioned that all dates are serialized into a numeric format, and that's so Excel can calculate the number of days that have passed between two dates.
It doesn't actually look at January 1, 2017 and January 30, 2017 to decide how many dates are in between there. It will serialize those numbers and then do the calculation and it does it all in the background for you, so you don't even realize it's happening. So day one in computer speak is January 1, 1900. Day two is January 2, 1900. So you can kind of get an idea of how the sequential dates occur.
January 1, 2017 is the 42,736th day since January 1, 1900. So if we were to serialize this date, it would return 42,736. And let's look at this date, November 12, 2016. If you in my number group, you can see that it's formatted as a Date. To simply and quickly serialize this number, I can change the format from Date to General.
There, that's the serialized number. That's the quick and easy way to do it, but just keep in mind, if on the exam you're asked to do this, they want you to use the DATE function. Now let's talk about time before we get into our worksheet. Time is very similar to how dates are serialized. Times in Excel are stored as decimals between zero and one. So a zero is zero hours.
0.25 is six hours. Remember there's 24 hours in a day, so a quarter of that would be six hours. And it makes sense that 0.5 is 12 hours. Now that we know how Excel is actually calculating our dates and times, let's go to our customer's worksheet. A couple of things want to point out here... Up in G1, I have used the TODAY function, =TODAY().
And remember this is volatile function, so each time we open this document, it's going to have your current system date in there. So when you open it, don't be surprised if it doesn't say March 1. It's going to be the current date on which you open the document. In our data, we have a column, column C, that gives us the date when our customers became a customer. Column D is where I'm going to demonstrate how to serialize a date using the DATE function.
And column E is where we will use our current date and our ending date to calculate the number of years between each of these dates. And once again, please keep in mind, when you open this file and do the exercise, your information in column E is going to be different than what I get today because I'm working off of March 1, 2017, so it's going to calculate based on that TODAY function. Alright, let's start with our first date in C10, and we're going to serialize it.
The function is =DATE. I'll go ahead and tab to step into it, and look at the syntax underneath. It's looking for the year, the month, and the day. So our year is 2004, the month is November, and the day is the twenty second. I'll go ahead and enter my closing parenthesis and hit Enter. Now I'm going to click back on D10 because you're probably scratching your head thinking, well I really didn't change anything, but look up at the Format at the top.
It pulled it in as a Date. I'm going to go ahead and change the format to General, and now I can see the serialized date for November 22, 2004. So that's how that DATE function works. Now let's move over to column E and work with our DATEDIF function. Let's type =DATEDIF, open our parenthesis, and we'll first begin by entering the start date. I'll click on C10, type in a comma, click on our end date, which is March 1, 2017 where we have used our TODAY function.
I want to make this an absolute reference so that I can copy the formula down. Type in a commma and then in quotation marks type a Y, so that it will return the number of years between these two dates. Go ahead and hit Enter. And 12 years have passed since November 22, 2004. Now if I'd like to see how many months have passed, I'm going to go up to my Formula Bar and just change that Y to an M, and hit Enter. We've had a 147 months pass since November 22, 2004.
Please practice using both the DATE and the DATEDIF functions to prepare for your MOS expert exam.
The course begins with an overview of the certification program and its costs. Next, Jennifer walks through all of the certification objectives, including hands-on experience with downloadable sample documents, so you can practice as you go. She wraps up with a full-length practice test that emulates exam 77-427, together with solutions to each of the exam challenges.
- Reviewing the exam format and learning objectives
- Tracking, retaining, and displaying all changes
- Identifying and troubleshooting errors with tracing
- Applying custom formats and layouts
- Applying advanced conditional formatting
- Using functions to format cells and apply advanced filters
- Preparing a workbook for internationalization
- Creating advanced formulas
- Using the LOOKUP and TRANSPOSE functions
- Using functions to serialize dates and times
- Creating advanced charts and tables