Locked lesson.

Upgrade

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:

2013, 2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Understanding Dates.xlsx
8.3 KB Understanding Dates - Completed.xlsx
8.7 KB Understanding Dates - Extra Practice.xlsx
11.5 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.
Login to download
  • 00:04 In this video we're going to look at dates inside Excel and how they work.
  • 00:08 So the first thing you'll notice is when I go and type in 6/20 in cell B3 and I hit Enter it converts it to a date the 20th of June.
  • 00:17 So what really happened there? Well 6/20 is how in a
  • 00:23 North American standard we would actually write any date. That would be June 20.
  • 00:28 Now how Excel knows to work with that is that it recognizes it as a potential date
  • 00:33 format and converts it into what it recognizes as a date which happens to be
  • 00:37 a value. And this is the trick to understanding dates. All I did was format that with a comma
  • 00:42 style but that actually shows the underlying value so the date format
  • 00:47 that you saw was just a mask on top of that. So what's this value? Well, 41,445 converts to June 20 because it happens to be 41,445
  • 00:56 days since January 1, 1900 that's sort of the day zero or day 1 for Excel.
  • 01:02 And the reason this is important is because we can actually perform mathematical operations on dates to make them return what we want.
  • 01:12 So if I take cell B3 plus 1 that will give me my next day 41,446. Which doesn't mean anything to me until I go and change this
  • 01:21 into a date format. At that point we can see that we've gone June 20 and June 21 because I've added 1 to the date.
  • 01:29 Now we have a nice handy little formula that we can work with called the
  • 01:34 TODAY function and what TODAY will do, it doesn't take any parameters just
  • 01:38 open bracket close bracket. When you hit Enter it will return the current date no matter what that date is
  • 01:43 when you open the workbook. So it always recalculates whenever the recalculation change is done.
  • 01:48 And notice that my formula that was referring to that original cell has increased by one so now its June 27 for me tomorrow's June 28.
  • 01:57 The other cool thing about dates is that we can actually go and start extracting individual components from them using the YEAR function
  • 02:04 =YEAR
  • 02:05 open the bracket and we'll pick that off cell B3 so =YEAR(B3)
  • 02:12 will return me 2013.
  • 02:13 The MONTH function will return me the numeric month so I can go and take the MONTH function against cell B3 and it returned six.
  • 02:23 And if I go and take the DAY function I can actually extract the day component directly from my date as well. So those are three
  • 02:31 handy functions for working with our dates to actually start pulling things apart.
  • 02:37 Now there of course may be opportunities where you need to actually put a date back together for some reason.
  • 02:42 So we can do that as well, we can say =DATE open our brackets and what DATE asks for is
  • 02:48 three particular parameters and we can feed any date that we want in there. So let's go and say 2013 comma
  • 02:54 will take us to the month argument. We can say 12
  • 02:58 go for December and 25. So this asked for it in a ISO format which is nice for international users
  • 03:04 of Excel here. We all understand this 2013,12,25 should give me Christmas Day
  • 03:10 when I hit Enter it seems to return that date for me and I can now tell you by formatting this as a number
  • 03:16 that that is day 41,633 and then if I go and change it back to
  • 03:24 a date it will actually come back and show me the date again. So again I could add one if I'm looking for Boxing Day or subtract 25
  • 03:32 if I want to go back to November 30 and do all those kind of things because underneath the mask that you see on top, every date
  • 03:40 is actually a number.

Lesson notes are only available for subscribers.

Gift this course