first Date functions challenges video.
I highly recommend you download your own copy of
this workbook because you’re going to want to try
this on your own before you watch the solutions.
Suzanne here, and I’m going to jump to the
Date Calcs on Birth Dates
Worksheet. So here we are. We have a date exercise
using a small selection of present and past
world leaders and each of the names has a link
going to a website that gives more information
about this person. But, what we’ve got for
our exercise: we have the birth dates, the
year they became a world leader, and then
some different calculations to complete.
To start things off we want to
enter a function for today’s date.
So, I’m going to click into this cell and enter
“=Today()” and the Today() function has no arguments.
And we see that this is April 6, 2023
when this video is being recorded.
So the first thing we want to do — the goal —
is to be able to get the approximate
age
when each of these people became a world leader.
And then, if they have passed
away, the age at which they died.
And if they’re alive: what their current
age is, what their birthday is this year,
and what their half year birthday would
be following the birthday this year.
And then I think maybe we’ll add one more:
has their birthday passed already this year?
That might be a good thing to also do in
this example. So, first to get the birth year
we’re going to use the Year()
Function applied to their birth date.
Then the birth month, we’ll use the Month()
Function applied to their birth date,
and the day of month they were born is
the Day() function applied to their birth dates.
And we can
put that function down the
column for each of those columns,
then we want to calculate the approximate age each
became a world leader. And we’re
going to do that just by subtracting
the year they became a world leader,
subtracting their birth year, from that year. So
G7 in the first row minus D7.
The year they became a leader
minus the year they were born.
Now, if we want to calculate the age
at which they passed away we only
want to show a value in this column if
we have a value in the death date column.
So in this case we’re going to go ahead and
use an IF() function and our Isblank() function.
We’re checking to see if it’s
blank in the death date column.
If it IS blank, then we are going to put
the age at death to also be blank, which
is just “” for an empty value in the
cell. Otherwise we want to subtract the year —
We want to start with the
year at which the person died
and subtract from that the
year that they were born,
to get the approximate age of death.
And then we need to close, okay I
think that’s gone so we’re blank here
we only have an approximate age of death for
two of these people and Nelson Mandela was
approximately 95 when he passed away,
and Shinzo Abe was 68. Now —
that was very recent — just this
past year when Shinzo Abe passed away. Now if they’re
alive we want to calculate their current age,
and we’re going to do a similar IF() function. If
the date is blank,
then we
presume they’re still alive because we
don’t have a date there and so we’re
going to calculate their current age and
we’ll do that by taking the YEAR of today,
the year of today’s date and subtracting from
that the birth year of the individual.
Otherwise we want to leave this cell
empty because they haven’t passed away.
And we’re doing something wrong,
and what are we doing wrong:
we have to put an absolute value on the
year of today because it is in ONE place
and we don’t want it to change when we
copy that formula down the column. So,
I’m going to use my F4 key to put dollar
signs in front of the column and the row
and we’ll try again.
So these look — we want to always
check and see if our values that we’re
getting as a result make sense and we see
that all three of these individuals were
born the same year so it makes sense
that their current age is similar.
Now, if we want to get the birthday this year,
this is a chance to use our Date() function.
We want the year
of today
and this is where we want to
remember to use the F4 key
so that we put the same
value in each row of this column.
And then we want the month for
the person in the current row,
and we want the day of month for
the person in the current row.
So June 1st, 2023 for someone
born June 1st, 1975 makes sense.
And now we can go another step further and say
half year birthday following this year’s birthday.
And I’m going to also do this one from
the beginning: the Date() function, the year
of today’s date,
absolute reference,
and then the month of the person’s
birthday but this time adding 6 to that,
six months in the future of this year’s birthday,
and then the day of the person’s birthday.
So we see that this one is
actually still in the same year
as is this one because the
birthdays are early enough.
But then some of these are in 2024.
Now let’s add one more column here:
Has the birthday passed already?
We’ll format it to look
like the one cell next to it. Now we want to know,
is this birthday already passed this year? And
we’re just going to
um say yes, if it’s already
happened or even if it’s today.
So, only if the birthday this year is greater
than today’s date are we going to say “No.”
If it is today’s date, we’re just going to say that
it has passed because we’re only going to have two
outcomes “Yes” or No.” We’re not going to have a
different outcome for the birthday being today.
So I’m going to go ahead and
format this, with some grid lines
and we’ll put a function in here: If this birthday
is greater than today’s date
then the birthday has not passed
else yes the birthday has passed,
because the dates in
the future are higher values than
the present date and dates in the past
are lower values than the present date.
So, if the birthday this year is greater than
today’s date then the birthday has not passed
yet. And we have to remember to put an absolute
reference on the reference to today’s date.
But we want to still have a relative reference
to the birthday of this person in this row.
So the birthday hasn’t passed yet for
someone whose birthday is on June 1st
the only person whose birthday has passed
is the person who was born in January,
Mateo renzi from Italy.
And that’s the end of this Date
Function Challenge. We have a second
Date Function Challenge: looking at
expiration dates – calculations. Thank you.