Focus video player for keyboard shortcuts
Auto
  • HD
  • 720p
  • 540p
  • 360p
1.00x
  • 0.50x
  • 0.75x
  • 1.00x
  • 1.25x
  • 1.50x
  • 1.75x
  • 2.00x
cc

We hope you enjoyed this lesson.

Get the Microsoft Excel 365 - Advanced course for more great video tutorials.

Start free trial

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.

Exercise files

Download this lesson’s related exercise files.

Understanding Dates
23 KB Understanding Dates - Completed
23 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.

 

Login to download
  • 00:04 Let's talk a little bit about dates in Excel.
  • 00:08 Now, dates have been the brunt of a lot of jokes with Excel, but
  • 00:12 there's actually a really good logic behind it if you understand what it is.
  • 00:16 Now, I'm going to start by extracting today's date and putting it in a cell.
  • 00:21 And the easiest way to do that is to use a formula that calls the TODAY function.
  • 00:26 Now, you'll notice when I hit Tab on this,
  • 00:28 that this function actually take zero parameters.
  • 00:31 It doesn't need it, you don't need to know today of what year because today is today.
  • 00:35 So it's just today, open close parenthesis, and we'll hit Enter.
  • 00:39 And what you'll see is that on my system,
  • 00:42 it returns 2022 09 22, so September 22, 2022.
  • 00:47 Now, the first thing you might be wondering is what the heck is with
  • 00:50 the format?
  • 00:50 How come mine looks different than yours?
  • 00:53 Well, that's because I have actually set up in my Windows
  • 00:56 control panel to use an ISO date as my standard,
  • 00:59 because I think this is how date should be displayed.
  • 01:03 If you're in America, you probably have 09 slash 22 slash 2022.
  • 01:08 If you're in England, it's probably 22 slash 09 slash 2022.
  • 01:14 And depending on where else you are in the world, it could be something different.
  • 01:17 But here's the part that gets super confusing, is when somebody takes a date
  • 01:22 that's in a valid format and goes and hits it with a comma.
  • 01:26 And you get this, 44,826.
  • 01:29 What the heck is that?
  • 01:32 Well, that is the number of days since January 1, 1900.
  • 01:38 Now, there's a little asterisk on that, there's a date bug that Excel actually
  • 01:41 made compatible with Lotus way back in the day.
  • 01:44 But for argument's sake, call it the number of days since January 1, 1900.
  • 01:49 Let me just go and format this back as a date.
  • 01:52 Now, this logic is actually super important to us, because it means that for
  • 01:57 tomorrow's date, we can actually take today's date and just add one day to it.
  • 02:02 And boom, we get September 23.
  • 02:04 That's brilliant.
  • 02:05 And you know what?
  • 02:06 If you open the completed example file for this,
  • 02:09 it will have whatever today's date is, and tomorrow's date's going to show you
  • 02:14 tomorrow because the TODAY function recalculates with the workbook.
  • 02:18 So, that's pretty cool.
  • 02:19 So, let's see what else can we do here.
  • 02:21 Let's extract the year.
  • 02:22 We've got a cool little function called YEAR.
  • 02:24 So, I'm going to go and get the year from today's date, and that returns 2022.
  • 02:29 I can grab the month from today's date, which will give me 09, and
  • 02:36 I can grab the day from today's date, which will give me 22.
  • 02:42 Okay, so this is really cool because everything
  • 02:44 is all based on one input that I've done with a formula here,
  • 02:48 and I've been able to extract a bunch of different components from it.
  • 02:51 Where this is really,
  • 02:52 really useful to me is that I can actually use this to build new dates.
  • 02:55 So check this one out.
  • 02:56 We're going to say, =DATE.
  • 02:58 And it says, what's the year?
  • 02:59 So I'm going to go and pick up 2022.
  • 03:02 What's the month I want to pick up?
  • 03:05 12.
  • 03:05 What's the day?
  • 03:07 Let's pick up 25.
  • 03:09 And this will return Christmas Day for the current year.
  • 03:15 And this will always return Christmas Day for the current year because Christmas Day
  • 03:20 is on December 25 and we're picking up the year based on today's date.
  • 03:25 So this is the cool thing that we can actually do to allow us to break things
  • 03:29 apart and put things back together the way that we may actually want to see them.

Lesson notes are only available for subscribers.

Gift this course