Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

Thread: Missing days with and without weekends

  1. #11
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Missing days with and without weekends

    Quote Originally Posted by treeppm View Post
    this step confusing bro. Copy B2 ok and paste to B3:B4 mean? we have to pate it in B3 and B4 too?
    Yes.

  2. #12
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Missing days with and without weekends

    Quote Originally Posted by treeppm View Post
    In this example how to get result in B5 like this (showing how many weekend days between the range)

    2019/06/05
    2019/06/07
    2019/06/10
    2019/06/13
    weekend days 2

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

    The other steps the same.

  3. #13
    Board Regular
    Join Date
    Jun 2015
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Missing days with and without weekends

    Quote Originally Posted by footoo View Post
    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....

  4. #14
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Missing days with and without weekends

    Quote Originally Posted by treeppm View Post
    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

  5. #15
    Board Regular
    Join Date
    Jun 2015
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Missing days with and without weekends

    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
    2019/04/09
    2019/04/10
    2019/04/11
    2019/04/12
    2019/04/15
    2019/04/16
    2019/04/19
    2019/04/22
    2019/04/24
    2019/04/25

    and more

    Thats why I want to know the formula..
    Last edited by treeppm; Jun 23rd, 2019 at 06:52 AM.

  6. #16
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Missing days with and without weekends

    Quote Originally Posted by treeppm View Post
    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.

  7. #17
    Board Regular
    Join Date
    Jun 2015
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Missing days with and without weekends

    Quote Originally Posted by footoo View Post
    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

  8. #18
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Missing days with and without weekends

    Quote Originally Posted by treeppm View Post
    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.
    Last edited by footoo; Jun 23rd, 2019 at 07:20 AM.

  9. #19
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Missing days with and without weekends


  10. #20
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,704
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Missing days with and without weekends

    Quote Originally Posted by treeppm View Post
    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
    2019/04/09
    2019/04/10
    2019/04/11
    2019/04/12
    2019/04/15
    2019/04/16
    2019/04/19
    2019/04/22
    2019/04/24
    2019/04/25

    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:

    Date Day Weekend Days In Date Range
    09/04/2019 Tuesday
    10/04/2019 Wednesday
    11/04/2019 Thursday
    12/04/2019 Friday
    15/04/2019 Monday 2
    16/04/2019 Tuesday
    19/04/2019 Friday
    22/04/2019 Monday 2
    24/04/2019 Wednesday
    25/04/2019 Thursday

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

    Code:
    =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(MIN(A2:A11)&":"&MAX(A2:A11))),2)={6,7}))
    Matty
    Last edited by Matty; Jun 23rd, 2019 at 10:47 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •