Instructor: All right, so let's talk
about three more date functions.
We're gonna talk WEEKDAY, WORKDAY, and NETWORKDAYS.
So starting with WEEKDAY, really valuable one,
it shows you which day of the week a given date falls on.
And there are two ways you can do this.
The first is just to use a custom cell format
like we talked about in lecture two.
So you can use either three d's
to get an abbreviated day of week
or four d's to get the full day of week.
And again, note that this isn't actually changing
the value or creating any kind of new value.
It's only changing how the original date value
is being displayed to you.
The second, which is the approach that I'd recommend,
is using the WEEKDAY function
which actually returns a serial value
corresponding to a particular day of the week,
either 1 through 7 or 0 through 6.
So the syntax is pretty simple.
You've got your serial number first,
which is the cell that contains a date or a time,
and then your return type, which is optional,
where 0 is your default
which assigns a number 1 to a Sunday
up to number 7 which is Saturday.
If you prefer to call Monday the start of the week
and assign 1 to Monday,
you could put a 1 here instead of a 0
or instead of leaving it blank.
So a really useful function,
especially when it comes to doing
things like day of week analysis
where you wanna see how performance or data differs
on a Tuesday versus a Friday versus a Wednesday and so on.
WORKDAY and NETWORKDAYS:
so WORKDAY, to be totally honest, is a little bit confusing.
Basically, it returns a date
that is a specified number of days
before or after a given start date
excluding weekends and holidays if you choose to add those.
So the most common application
that I can think of is to say,
what date falls 50 work days from today?
So once I get through 50 work days,
which doesn't include weekends,
what date am I gonna land on?
And so syntax-wise, it starts with your start date,
then goes to the number of days.
You can either put a positive value here
if you wanna look forward in the future
or a negative value if you wanna look back in the past.
Those are all the required components.
There's an optional holidays component that lets you
reference a list of holidays
and it will essentially exclude those
from the number of working days that it's calculating.
So that's WORKDAY.
NETWORKDAY, which I actually find much more useful
and more practical,
shows you the number of WORKDAYs between two dates.
So you input a start date, an end date,
and an optional reference to a list of holidays,
and it will tell you how many work days
or working days fell between those dates.
So two examples here.
I've got the same two date references
that I mentioned in the last lecture,
which are January 1st, 2015 as our start,
February 28th, 2015 as our end.
So if we say WORKDAY B2, 20,
that's saying from the start date of January 1st, 2015,
what date falls 20 work days after that?
And it will return the date: January 29th, 2015.
If we use the NETWORKDAYS function
and set B2 as our start date and B3 as our end date,
it will tell us that between January 1st
and February 28th, 2015,
there were 42 working days
which is the total number of days minus all of the weekends.
So let's jump into Excel
and see kind of how this works in practice.
So in F3, I've got a Project Due Date cell,
and I'm just gonna make up some date that means nothing.
So let's say May 9th, 2017.
This is a big project.
I mean, it's gonna take a lot of time.
Days until due:
this is really simple.
Since date values are really just values
that I can perform any normal operation on
just like I would any other numbers,
I'm just gonna say the due date minus the current date.
Now it returns 630.
So I have 630 days
between August 18th, 2015
and May 9th, 2017.
So that's the total days until due.
Now, if I want the number of work days until due,
'cause obviously I'm not gonna spend my weekends
working on this project,
I can use NETWORKDAYS.
So NETWORKDAYS, start date
is current date, 8/18.
End date is the due date.
And so if I leave the optional holidays field blank,
close the parentheses and press Enter,
that's telling me
that while I have 630 total days until it's due,
I only have 451 working days until it's due.
So for the holidays piece,
what I can't do is just type in a number of holidays
like 5 and say, okay,
exclude those five holidays.
It'll just return the same value of 451.
What it needs here is an actual reference to a list of dates
that define the holidays that I want to exclude.
So what I can do, just for the sake of an example,
let's say 12/25/2015,
I wanna take off Christmas
and I wanna take off January 1st, 2016.
And those are the two holidays
between today's date and May 9th, 2017
that I plan to take off and not work.
So what I can do is say, okay,
instead of putting a 5 there,
I'm gonna refer to H5 through H6,
which is my list of holiday dates,
and press Enter.
And now you can see it updated to 449.
So it excluded these holiday dates as well.
Note that if you have a holiday
that also falls on a weekend,
it would've already been excluded here.
But in this case,
both of these dates are weekdays
so they were being counted in the 451 total.
And so when I add that reference in,
it excludes those two
and leaves me with 449 working days
until the project is due.
Last but not least, for Day of Week,
again, I could just right click and go into Format Cells
on the actual date field and say,
let's just do a Custom,
delete this type line and put dddd,
say OK, it's a Tuesday.
But I want to continue to show the date
in its current format
and just add the day of week separately here.
So I'm gonna use the WEEKDAY function: =WEEKDAY
of the serial number, which is F3,
and I'm gonna leave the return type default.
So I get a 3.
And when I format the 3,
I can change that to custom, dddd,
and there you go.
It gives me Tuesday.
So May 9th, 2017 is a Tuesday.
So there you go.
There's WEEKDAY, WORKDAY, and NETWORKDAYS.