Focus video player for keyboard shortcuts
Auto
- 720p
- 540p
- 360p
1.00x
cc
- 0.50x
- 0.75x
- 1.00x
- 1.25x
- 1.50x
- 1.75x
- 2.00x
We hope you enjoyed this lesson.
Cool lesson, huh? Share it with your friends
About this lesson
Understand how dates work in Excel using the TODAY, YEAR, MONTH, DAY and DATE functions.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Understanding Dates11.4 KB Understanding Dates - Completed
11.7 KB
Quick reference
Topic
Understanding how dates work in Excel using the TODAY, YEAR, MONTH, DAY and DATE functions.
When to use
In order to reduce error potential, we need to try and enter data as few times as possible. Using Excel’s Date functions, we can manipulate a date in a single cell into every format of the date that we could possibly need.
Instructions
Key Understandings
- Dates are just a number mask put on a numerical value
- Each date’s “serial number” (underlying value) is the number of days since Jan 1, 1900
- Because dates are values we can add and subtract days from them
Formula Examples
|
=TODAY() |
Will return the current date, updating each time Excel recalculates. |
|
=YEAR(A1) |
Will extract the 4 digit year from the value in cell A1. |
|
=MONTH(A1) |
Will extract the 1-2 digit month from the value in cell A1. |
|
=DAY(A1) |
Will extract the 1-2 digit day from the value in cell A1. |
|
=DATE(2013,12,25) |
Will convert the value into the numerical value representing 2013-12-25. |
|
=TODAY()+1 |
Will return tomorrow’s date, updating each time Excel recalculates. |
- 00:04 One of the things that a lot of people don't understand with regards to Excel is
- 00:09 how dates are put together.
- 00:11 So sometimes they'll go and they'll format them with a different number format and
- 00:15 they'll come up with a really weird number and they're wondering what the heck is
- 00:17 going on, so we're going to remove that mystery for you right now.
- 00:20 To start with though, what I want to do is I want to give you a very special formula
- 00:25 that is called Equals Today and this would give you today's date.
- 00:29 Now notice that there is no parameters that the this actually needs.
- 00:32 I've got an open bracket, but there's no IntelliSense coming back here any more.
- 00:36 The Today function doesn't need any parameters,
- 00:38 doesn't take any parameters, we just type in =TODAY open parenthesis,
- 00:42 closed parenthesis and hit Enter, and it will return today's date, so
- 00:45 you can see that I'm actually recording this on December 22nd, 2015.
- 00:50 This date will recalculate each time the workbook is recalculated.
- 00:55 So If it's June 30th, 2016 when you open this workbook,
- 00:59 you'll see that that will be the date that will show inside the cell.
- 01:04 Now, what is it about dates though that are confusing?
- 01:07 Well, let's try this.
- 01:08 Let's select the date, and let's hit this with a number style, a comma style, and
- 01:12 we get back a value that looks like 42,360 and
- 01:15 this is where people tend to get a little bit concerned wondering what happened.
- 01:20 This is what we call a date serial number, and
- 01:22 what it's doing is it's actually counting the number of days since January 1st 1900.
- 01:28 That's what a date is really built like in Excel.
- 01:32 Now, the challenge here that we kind of aren't used to seeing,
- 01:35 is the date serial number itself.
- 01:37 What we prefer to see, is we prefer to see when this is actually covered in a date
- 01:42 format, but this format is essentially just a mask on top of the real number.
- 01:47 So knowing that, knowing that this is a date serial number that's underlying this,
- 01:51 this actually gives us the flexibility to do some things like add values to dates,
- 01:55 and if we add a number to a date, we can actually advance to new days.
- 01:59 So let's take a look here where we have tomorrow.
- 02:02 We could say =TODAY+1, and that will add one to the date serial number
- 02:07 which will then get converted into December 23rd.
- 02:11 We can also extract components from dates.
- 02:14 We can say =YEAR and pick up December 22nd, 2015, and it will return 2015.
- 02:21 This is true whether the date is displayed as a number with the date serial number,
- 02:27 or as the actual date itself that we're actually looking at in this particular
- 02:31 mask and the reason being is just like with the month function,
- 02:36 it's actually asking for a date serial number.
- 02:39 It doesn't care that you're giving it a date because it knows underlying this
- 02:42 is actually a real value.
- 02:43 It parses that and comes back with the proper month, 12.
- 02:47 We can also extract the day.
- 02:52 Again it's asking for the date serial number, we provide the date, and
- 02:55 it comes back with 22.
- 02:57 And this is kinda cool because this means that we can actually not only
- 03:01 take dates apart, but we also have some abilities to put dates back together and
- 03:06 we can do that either by putting the whole thing back together or
- 03:08 by creating our own special date.
- 03:10 So let's take a look day Christmas Day.
- 03:12 If we wanted to say =DATE, it asks for what year?
- 03:17 So you know what, let's go grab 2015 and it says what month would you like?
- 03:22 Well, Christmas Day is always on December 25th, so
- 03:26 we'll go with comma 12 comma 25 and we'll hit Enter.
- 03:31 And now the beauty is this tells me that Christmas day this year is
- 03:35 on December 25th, 2015.
- 03:36 No surprise here except that when today's date advances to say
- 03:41 June 23rd, 2016, notice that my Christmas Day
- 03:45 now automatically updates to pull a 2016 year end.
- 03:50 So that's a really cool thing with our date formats,
- 03:54 is that we can actually rely on these and use different functions to
- 03:57 actually strip these dates apart, and also to put them back together.
Lesson notes are only available for subscribers.