Locked lesson.

Upgrade

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.xlsx
11.8 KB Understanding Dates - Completed.xlsx
12.1 KB

Quick reference

Topic

Understanding dates.

Description

Understanding how dates work in Excel using the TODAY, YEAR, MONTH, DAY and DATE functions.

Where/when to use the technique

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 gonna look at understanding how dates work in Excel,
  • 00:07 because there's some tricks to understanding what's actually
  • 00:10 going on here.
  • 00:11 I'm gonna start by writing a simple little formula here, which is =today.
  • 00:16 And we took open parenthesis and close parenthesis.
  • 00:19 Takes no parameters whatsoever.
  • 00:21 And we'll say enter.
  • 00:22 And you now know what day it is that I'm actually recording this video.
  • 00:26 The today function will always recalculate every time the workbook is recalculated to
  • 00:30 return the current system date from the system that you have.
  • 00:33 So in this case you can see it's October 4th.
  • 00:36 Now why is this important?
  • 00:38 We'll let's go and take a look at this here, how it actually works.
  • 00:41 If I convert this to a number, it's gonna come back to a value 42,281.
  • 00:46 That number actually indicates the number of days since January 1, 1900.
  • 00:51 Now.
  • 00:53 This is kind of cool because this means that when we actually go and
  • 00:57 use dates inside Excel they actually are numbers and
  • 01:00 that's cool because I can now say equals today's date plus one and
  • 01:06 it will run that mathematical formula and give me tomorrow's date.
  • 01:11 I've also got some really nice functions that I can use based on a date's serial
  • 01:14 number or a date, like year.
  • 01:17 And this will allow me to actually go and extract just the year from a date.
  • 01:23 Or let's say the month.
  • 01:26 We can take the month from today's date and
  • 01:28 it should come back with 10 for October.
  • 01:31 Unfortunately, it doesn't give you text.
  • 01:32 That's the whole different way of dealing with things.
  • 01:34 But if we go back, we can extract day as well.
  • 01:39 Now this has huge ramifications for us because it means that we can
  • 01:43 actually guard against problems in data rather than actually putting in a year,
  • 01:47 a month, a day into separate areas inside the spreadsheet.
  • 01:50 We can actually enter a date once and use formulas to split it apart.
  • 01:55 I'll tell you a quick story.
  • 01:56 When I first started working in a golf course,
  • 01:59 we had a daily sales report that involved somebody putting in the full date,
  • 02:03 which you can see at the top here for each day of sales that we dealt with.
  • 02:06 And then they had another area where they had to fill in the year and the month and
  • 02:11 the day and the day in text format and the week and all these different pieces.
  • 02:15 That gave lots of opportunity for us to make errors when we were putting things in
  • 02:20 so the better way of dealing with this is putting in the date once and
  • 02:24 writing a formula that breaks apart all the individual pieces we need.
  • 02:29 But like with other formulas,
  • 02:31 we're not restricted just breaking things apart using Excel formulas.
  • 02:34 We can actually put things back together.
  • 02:36 So we can actually build a date from different components.
  • 02:40 If I wanted to find for example say,
  • 02:42 Christmas day this year, I could build a little formula to do that.
  • 02:45 I could say, all right, let's go with date.
  • 02:48 And I'll open my brackets, and you'll notice that it asks for a year,
  • 02:51 and I'm gonna say well you know what, let's go with a year from today's date,
  • 02:56 and then it says which month would you like to go with,
  • 02:59 and I'll say well I'm gonna go with 12.
  • 03:01 Because that's December, and what day is Christmas day, it's the 25th.
  • 03:06 So I'll put that in as well, and
  • 03:08 you'll notice that it will return December 25th, 2015.
  • 03:12 So, that's kind of a really nice thing about Excel here,
  • 03:17 is that you have the ability to take individual cells and put them together.
  • 03:22 So, let's say that I wanted to go and build a new date seven days away from
  • 03:27 what I had before I could say equals date, I could say equals 2015
  • 03:32 Comma 10 comma 4 plus 7 and I would be able to build October 11th.
  • 03:39 So a new date out of different individual pieces and components.
  • 03:43 This is what the real real good stuff about these date formulas is.
  • 03:47 You can take it apart, you can put it back together and
  • 03:49 you can drive your solutions by doing that with a single input in many many cases.

Lesson notes are only available for subscribers.

Gift this course