Date() Function Video.
Suzanne here. If you haven’t already done
so, I recommend you download this file,
because you’ll be able to experiment a
lot and look around and learn about dates.
We’re going to start by looking at the Date
Functions Defined worksheet, because this is
a good way to see the arguments of the different
date functions we’re covering here. The only
one we’re talking about specifically in this
video is the actual Date() function, and that
function has three arguments:
the year, the month, and the day.
And they are entered into that function
in that order, separated by commas, and
what the Date() function returns is the
date represented by the year, month,
and day given to it, and it returns it as
a date serial number in Excel, that can be
formatted as a date, or it could be formatted as a
number. But it’s a number representing that date,
and it would be the number of days from
January 1st 1900 to the date in question.
So, let’s take a look at some opportunities to use
the Date() function. On this worksheet, we have
today’s date in cell C6 and then we also have
a particular birth date entered in cell C7.
Now, we might like, once we
know somebody’s birth date
we might like to be able to construct
what their birthday this year would be,
and the Date() function allows us to do that. So,
in this example we’re using the Date() function,
giving it the year of today’s
date, the YEAR from the date in C6.
And then, we’re giving it the MONTH of
the birth date, that’s over here in G7,
which happens to be 12, we can see it here. We’re
feeding that MONTH into the Date() function, and
then the DAY argument, in the date function, we’re
getting that from the cell containing the DAY
from the birth date in C7. We got that DAY
using the Day() function and we’re combining
that YEAR the MONTH and the DAY and we’ll
come out with the date December 31st 2023.
And, this is formatted as a Date but if you change
it to General format, now it’s a number that’s the
date serial number 45291 that represents December 31st, 2023.
And that is the number of days since
January 1st, 1900 when Excel tracks
time from. There are no dates before
January 1st, 1900 in an Excel spreadsheet.
So I’m going to format that back to a date format
and go on to another example using the Date()
function. Let’s say we want to know
upon what date someone with this birthday
would reach the age of 70 and a half years old.
We use the Date() function, and we feed
it the year they were born plus 70
as a first argument, the YEAR argument, then for
the MONTH argument, we give it the month they
were born plus six so they would be 70 and
a half, (six months after the 70th birthday)
so we’ve got the month they were born plus six,
and then we’ve got the DAY they were born,
the DAY of the month they were born. So
that would be the date: July 1st, 2008
was the year someone with this birth
date turned 70 and a half years old.
You can calculate the date when they reached
72 years old, that’s going to be their birthday
that year because we’re not asking for it to
be half year, so we would in this case give the
year of birth plus 72 and then the MONTH
of birth and the DAY of month of birth.
And here’s one other challenge: the half
year birthday following this year’s birthday.
In this case we’re taking the YEAR of today,
and the MONTH the person was born plus
six, for the MONTH argument to the Date(),
and then the DAY they were born of the month, the
day of the month. So that combination would give
the half year birthday following their birthday
this year. So their half year birthday next year.
That is the end of the
examples of the Date() function in use. Come
back to the Date() Function Challenges video
to try your own hand at this on some of the
worksheets in here and see the solutions. Thank you.