workbook we’re going to be looking at date calculations related to expiration dates. And, if you haven’t
already done so I recommend you download your own copy of this workbook so you can try this out for
yourself before following along with the solutions. So, I’m going to go ahead and click on the Date Calcs
Food Quality worksheet, and what you have here is a set of different food items listed in this column.
And, according to a website you can find, if you’d like to take a look at it, these are the days, months, or
years, these items should typically last if stored in your refrigerator. So what we’re gonna do in this
worksheet is we’re going to calculate, based on today’s date, what the projected expiration of the item
would be if purchased today. So first, we’ll enter a function to bring in today’s date.
The Today() function.
And then it’s going to be useful to have the year, month, and day of today’s date broken out for us to use
in generating the projected expiration dates. So we’re going to use the Year() function, the Month()
function, and the Day() function to get those values.
I’m applying the Year() function to today’s date,
and now the Month() function,
and the Day() of month function.
Now you’ll notice we’ve got these several different food items, and then we have their time till expiration
defined in either days, months, or years.
And we’ve taken care to be sure that the cells that don’t have numbers in them are empty cells. They
don’t have a value, a hidden value of any kind. And
the way we’re going to calculate the date of expiration, we’ll use the Date() function which has three
arguments: year, month, and day, and our projected expiration is going to be the year of today, which we
have already in this cell, plus
any year value there is in the projected time till expiration column.
Then we’re going to get the month from the month of today,
plus any month value in the months till expiration column.
If it’s empty, it’s just going to add 0 to it, no value. And then for the day argument to the Date() function,
we’ll use the day of the month of today’s date, plus the days till expiration value in that column. Now
one thing we have to remember to do here is put absolute reference on the values for the year, month,
and day of today’s date, because we want that cell reference to stay the same as we copy this formula
down this column. So, I’m using the F4 key to make the C5 reference absolute and the D5 reference and
the E5 reference.
And now I’ll copy that formula down the column, and we want to check and make sure that the way we
did this got the results we should expect. So we have apples with the projected
21 days to last in the refrigerator and today’s date is April 6th and they’re projected to last until April
27th. Let’s take a look at one that has months till expiration. Hard cheese unopened. April 6th is today’s
date and that’s predicted to last six months into the future till October 6th. And then let’s look at
something that’s projected to last a year, the maple syrup.
And that’s projected to last till April 6, 2024. Now let me just say that even though we didn’t have any
examples like that, if we had months AND days in here, say something was projected to last one year, six
months, and five days, this formula would still work. It doesn’t only work if there’s values only in one of
these columns. But the main thing is that the cells without any value are actually empty. And we can test
that if we apply the Isblank() function to one of these cells and confirm that it is true that that cell is
blank, there’s not any hidden value in there.
And that’s the end of our second Date() Function Challenges video. Thank you.