Auto news on Youtube Jan 3 2019

How can you figure out how many Fridays

are included between two dates?

This question was raised by Hussein,

who wrote, "Please help me find a formula

"that counts the number of Fridays

passed in a month till today."

For example, if our start date

is 3rd of December 2018,

and our end date is 22nd of December 2018,

the answer should be three.

What do ya think?

How can we solve this?

(hip hop music)

In Excel, we don't directly have a formula

that calculates the number of Fridays

between two dates, but we can use

the existing functions and formulas

to get the result that we want.

One formula that gives us the number of days

between two days, super simple formula,

is just to deduct them from one another.

I'm gonna go to end date minus start date.

This way, I can get the number of days

that are between these two dates.

Now remember, dates are just numbers in Excel,

so if I switch the formatting from date

to general, I see the number that's associated

with this date.

They start counting from the 1st of January 1900.

If I switch this to one

and I switch back the format to date,

we can see 1st of January 1900.

Let me just press Control + Z to go back

to what I had.

We have another formula that also helps us

to calculate the number of days

between two dates, and exclude Fridays.

We're basically exclude certain days

from our calculation.

That's called the NETWORKDAYS formula.

Now NETWORKDAYS originally assumed

that the days it should exclude

are the weekends.

The original NETWORKDAYS formula

just requires the start date and the end date.

It automatically excludes weekends.

It assumes that weekends are Saturdays and Sundays.

But since Excel 2010, we have a new formula

that is called NETWORKDAYS.INTL,

so network days international.

We can actually define the type of weekend

that we have in our country.

Start date, that's the same.

End date is right here.

Now for weekend, we can pick Saturday,

Sunday, or we can scroll down here

and pick Friday only.

If you wanted to exclude, for example,

Mondays only, we would select 12 here.

But in this case, we're gonna select 16.

What this does is it counts the number

of days between the two dates

and it excludes Fridays.

Now I can use the information from these two

to calculate how many Fridays I have.

But there's one thing I have to be careful with.

That's what these days actually

include and exclude.

Basically when I get the difference between

these two dates, do I include the start date

and the end date?

Or is one of them or both of them excluded?

That's something we have to take care of

before we end up deducting one from the other.

To check that out, let's just restrict

our date range to smaller ones.

We can just quickly take a look at calendar

and find out what's included and excluded.

I'm gonna switch that to the 6th.

That's from Monday, the 3rd, to the 6th,

that's a Thursday.

There's no Friday included.

Look at my results.

I get different answers.

How many days between these two dates?

I get three.

One, two, three.

One of these days is not included.

It's excluding the start date.

It counts one, two, three.

Whereas this one, the NETWORKDAYS,

includes both the start date and the end date.

To be able to compare them to one another,

I need to make an adjustment.

I'm gonna add a one to this.

In the first case, I'm also including

the start and the end dates.

Now if I wanted to calculate how many Fridays

are between these two dates, well,

I have all the information I need.

I have how many days are in between these two.

I have how many days are between these two,

but exclude Fridays.

All I have to do

is do this minus this.

That's my answer.

If I switch this to the 8th of December,

I get one.

From the 3rd to the 8th, there's one Friday.

If I switch this to the 22nd of December,

I get three.

That's one Friday, two and three in here.

Now I could write this as just one formula.

That's gonna be end date minus start date.

I'm gonna add the one to this.

Minus the NETWORKDAYS.INTL.

Start and end 16

to exclude Fridays from that.

(laughs) Now I get a date because Excel thinks

I want a date.

I don't, I just want a number,

so I'm gonna switch that to the general format.

I can also take a look at a bigger timeline.

Let's just switch that to 2019.

Let's look at January 31st.

I get eight Fridays between the two.

Now I have the January calendar hidden in there.

Let's just go to the selection pane

and unhide my picture.

Let's take a look.

That's between the 3rd and the 31st.

That's one, two, three, four,

five, six, seven, eight Fridays

are included between these two dates.

The NETWORKDAYS formula, together with the

other date formulas, are one of the formulas

I cover in my free e-book that's called

Top 10 Essential Excel Formulas to Work Smarter.

It's an absolutely free e-book.

If you're interested to get a copy of it,

jump over to my website,

register and grab your copy.

The link to it is in the description

of this video.

Now as always, if ya like this video,

give it a thumbs up.

If you'd like to get more tips and tricks,

consider subscribing.

(upbeat music)