- 720p
- 540p
- 360p
- 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 Dates21.8 KB Understanding Dates - Completed
19.5 KB
Quick reference
Understanding Dates
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:05 To talk about dates, I'd like to start by giving you a very special function for
- 00:09 Excel, and it's called =TODAY.
- 00:11 And you'll notice that this function,
- 00:13 when I hit Tab, it opens the parenthesis exactly as you would expect.
- 00:17 But you'll notice that in the intellisense there's no request to put
- 00:20 anything in here.
- 00:21 And this is one of the reasons this function is special,
- 00:23 is because it takes no parameters.
- 00:25 We just type equals today, open parentheses, close parentheses and
- 00:29 hit Enter.
- 00:30 And at that point, it will return the actual date that is today's date.
- 00:36 And the beauty is that every time you open this workbook it's gonna recalculate.
- 00:39 So if you actually look at this one,
- 00:41 you can see that I've recorded this particular video on November 14th, 2018.
- 00:46 Now, talk I wanna about looking at this,
- 00:49 the part of dates that actually freaks a lot of people out.
- 00:52 And it's when you take a date like this, and you accidentally go and
- 00:56 hit it with a comma style.
- 00:57 And you get this value 43,418, what is that all about?
- 01:02 What this is, is this is actually a date serial number.
- 01:06 This is the number of days since January 1st, 1900, because
- 01:10 that's the day that Excel thought was the first day in the history of the world.
- 01:14 Now, it's not a big here, we can very simply go and reformat this is a Date.
- 01:20 There we are, Short Date and here we go, we're back where we are.
- 01:23 But if you were accidentally format a cell with a numeric value,
- 01:26 that's what's happen.
- 01:27 This is actually a really good thing, because knowing that this is a date serial
- 01:31 number that actually lives underneath the cell, we can do something like this.
- 01:35 We can say =, take our date and say, +1, and this will give us the next date.
- 01:43 The great thing about this too though,
- 01:44 is that we have more formulas that we can actually work with.
- 01:47 So for example, if we have a date, like today's date, I could go and says,
- 01:51 =YEAR, and I could extract the year from this particular date.
- 01:57 And as you can expect, it gives me 2018.
- 02:00 If I want the month, I can say =MONTH, and we can go and
- 02:04 grab the date again, close our parenthesis, and we get to 11.
- 02:08 And naturally the day can be extracted using the DAY function, just like that.
- 02:14 Why this is super important and powerful is because it allows us to enter a date
- 02:18 once into a cell, and break apart the difference components that we might need.
- 02:22 So if we need a column header for a report that says 2018 on it,
- 02:25 I can still have someone enter today's date, and then extract that value.
- 02:29 I don't have to have somebody enter the same thing in multiple places.
- 02:31 And if we can reduce the amount of data entry we reduce the amount of
- 02:36 potential errors.
- 02:37 Now just like with text functions and everything else,
- 02:41 once we've actually broken things apart we can also put them back together.
- 02:44 We have a really cool function for dealing with that too, this is called DATE.
- 02:49 And you'll notice that DATE says give me a year, so I could say,
- 02:52 well, no problem, let's go and grab the date year right here, or
- 02:56 we could feed it in with YEAR function.
- 02:59 If I wanted to build Christmas, we always know that it's on December comma 25.
- 03:04 And what we'll find out right now is that Christmas Day is, of course,
- 03:09 December 25th, 2018.
- 03:11 Why is this super important?
- 03:13 Well, because right now if you open this particular module,
- 03:16 you're not gonna see this date, because you're getting it way after I built it.
- 03:19 Let's say that you went and you opened this on say June 30th, 2020.
- 03:25 And just like that, tomorrow's date updates,
- 03:28 Christmas Day updates, the 2020 year end as well.
- 03:32 And this is one of beautiful things around that TODAY function,
- 03:35 is that it actually automatically updates every time you open your workbook or
- 03:38 every time you recalculate your workbook to get to today's date
Lesson notes are only available for subscribers.