Missing days with and without weekends

[treeppm]

Hi fellows
I would like to know the missing day between the range that exclude weekends(Saturday, Sunday)

in the following example from the date range A1 to A4 what is the formula we have to write in B2 and B4 to get result like this?

[TABLE="class: grid, width: 350, align: left"]
<tbody>[TR]
[TD]2019/06/05[/TD]
[TD]Missing Days with out week end[/TD]
[/TR]
[TR]
[TD]2019/06/07[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2019/06/10[/TD]
[TD]Missing Days with week end[/TD]
[/TR]
[TR]
[TD]2019/06/13[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


Kind Regards

 

[footoo]

this step confusing bro. Copy B2 ok and paste to B3:B4 mean? we have to pate it in B3 and B4 too?

Yes.

 

[footoo]

In this example how to get result in B5 like this (showing how many weekend days between the range)

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]2019/06/05[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2019/06/07[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2019/06/10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2019/06/13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]weekend days[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

Kind Regards

B2 formula = A2 -A1 - 1 - NetworkDays(A1 + 1, A2 - 1)

The other steps the same.

 

[treeppm]

B2 formula = A2 -A1 - 1 - NetworkDays(A1 + 1, A2 - 1)

The other steps the same.

Yo dude, please kindly Check. it's B2? or it's B5?.
and there is no calculation A3,A4....

 

[footoo]

Yo dude, please kindly Check. it's B2? or it's B5?.

B2

What do you mean : and there is no calculation A3,A4.... ?

Just perform the steps set out in post # 8

 

[treeppm]

I think you take this I'm only using this 4 Row and columns,, but I'm taking this 4 row and columns for example the reality is I've dates like

I've to calculate
[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD="width: 76, align: right"]2019/04/09
[/TD]
[/TR]
[TR]
[TD="align: right"]2019/04/10[/TD]
[/TR]
[TR]
[TD="align: right"]2019/04/11[/TD]
[/TR]
[TR]
[TD="align: right"]2019/04/12[/TD]
[/TR]
[TR]
[TD="align: right"]2019/04/15[/TD]
[/TR]
[TR]
[TD="align: right"]2019/04/16[/TD]
[/TR]
[TR]
[TD="align: right"]2019/04/19[/TD]
[/TR]
[TR]
[TD="align: right"]2019/04/22[/TD]
[/TR]
[TR]
[TD="align: right"]2019/04/24[/TD]
[/TR]
[TR]
[TD="align: right"]2019/04/25[/TD]
[/TR]
</tbody>[/TABLE]

and more

Thats why I want to know the formula..

 

[footoo]

I think you take this I'm only using this 4 Row and columns,

I don't think that.
Copy the B2 formula down as far as required.
It seems to me you haven't tried what I've suggested.
If you do, it should be clear to you - if it's still not, I give up.

 

[treeppm]

B2

What do you mean : and there is no calculation A3,A4.... ?

Just perform the steps set out in post # 8

Bro,, Please check what you have given solution in Post 8
it's
"Assuming your data starts in A1 (which contains 2019/06/05)
• Enter in B2 =NETWORKDAYS(A1+1,A2-1)
• Copy B2 and paste to B3:B4
• Enter in B5 =SUM(B2:B4) "

we should not touch anything from B1 to B4 because it has other things.

we have to do the calculation only B5 and B6
B5 calculation should count the missing day values excluding weekend.
and B6 calculation should count the missing day values including weekend.

or now you can give me one simple solution how to calculate number of weekend days between two selected range

Kind Regards

 

[footoo]

Bro,, Please check what you have given solution in Post 8
it's
"Assuming your data starts in A1 (which contains 2019/06/05)
• Enter in B2 =NETWORKDAYS(A1+1,A2-1)
• Copy B2 and paste to B3:B4
• Enter in B5 =SUM(B2:B4) "

we should not touch anything from B1 to B4 because it has other things.

we have to do the calculation only B5 and B6
B5 calculation should count the missing day values excluding weekend.
and B6 calculation should count the missing day values including weekend.

or now you can give me one simple solution how to calculate number of weekend days between two selected range

Kind Regards

Can't think of a single formula to do what you want. Perhaps someone else can.
My suggestions are based upon using helper column(s).

"we should not touch anything from B1 to B4 because it has other things."
I really need to improve my mind reading abilities.

 

[footoo]

see also : https://www.mrexcel.com/forum/excel...culate-how-many-weekend-days.html#post5298798

 

[Matty]

I think you take this I'm only using this 4 Row and columns,, but I'm taking this 4 row and columns for example the reality is I've dates like

I've to calculate
[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD="width: 76, align: right"]2019/04/09[/TD]
[/TR]
[TR]
[TD="align: right"]2019/04/10[/TD]
[/TR]
[TR]
[TD="align: right"]2019/04/11[/TD]
[/TR]
[TR]
[TD="align: right"]2019/04/12[/TD]
[/TR]
[TR]
[TD="align: right"]2019/04/15[/TD]
[/TR]
[TR]
[TD="align: right"]2019/04/16[/TD]
[/TR]
[TR]
[TD="align: right"]2019/04/19[/TD]
[/TR]
[TR]
[TD="align: right"]2019/04/22[/TD]
[/TR]
[TR]
[TD="align: right"]2019/04/24[/TD]
[/TR]
[TR]
[TD="align: right"]2019/04/25[/TD]
[/TR]
</tbody>[/TABLE]

and more

Thats why I want to know the formula..

Given the above example, I work out that there are 4 weekend days in that range:

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Date[/TH]
[TH]Day[/TH]
[TH]Weekend Days In Date Range[/TH]
[/TR]
[TR]
[TD]09/04/2019[/TD]
[TD]Tuesday[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/04/2019[/TD]
[TD]Wednesday[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/04/2019[/TD]
[TD]Thursday[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/04/2019[/TD]
[TD]Friday[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15/04/2019[/TD]
[TD]Monday[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]16/04/2019[/TD]
[TD]Tuesday[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19/04/2019[/TD]
[TD]Friday[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22/04/2019[/TD]
[TD]Monday[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]24/04/2019[/TD]
[TD]Wednesday[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25/04/2019[/TD]
[TD]Thursday[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If this logic is correct, it can be achieved via formula as follows:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(MIN(A2:A11)&":"&MAX(A2:A11))),2)={6,7}))

Matty