Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Missing days with and without weekends

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

    Question Missing days with and without weekends

    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?

    2019/06/05 Missing Days with out week end
    2019/06/07 3
    2019/06/10 Missing Days with week end
    2019/06/13 5


    Kind Regards
    Last edited by treeppm; Jun 22nd, 2019 at 08:34 AM.

  2. #2
    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

    Formula with WORKDAYS working?

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

    Default Re: Missing days with and without weekends

    =NETWORKDAYS(A1+1,A4-1)+2-COUNT(A1:A4)

    =A4-A1+1-COUNT(A1:A4)
    Last edited by footoo; Jun 23rd, 2019 at 01:58 AM.

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

    Default Re: Missing days with and without weekends

    This is not correct :
    =NETWORKDAYS(A1+1,A4-1)+2-COUNT(A1:A4)

    Instead, put in B2 =NETWORKDAYS(A1+1,A2-1), fill down and sum the results
    Last edited by footoo; Jun 23rd, 2019 at 02:10 AM.

  5. #5
    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
    put in B2 =NETWORKDAYS(A1+1,A2-1), fill down and sum the results
    Bro, sorry I can't understand "fill down and sum the results"
    kindly explain it

    Regards

  6. #6
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,574
    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, sorry I can't understand "fill down and sum the results"
    kindly explain it

    Regards
    Copy the formula in B2 and paste to B3:B4, then sum B2:B4

  7. #7
    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

    Bro., that's extremely confusing me. I think you are also little bit confuse because of my table. here you go. if the table being like this
    2019/06/05
    2019/06/07
    2019/06/10
    2019/06/13
    Missing Days without weekend 3
    Missing Days with weekend 5


    Kind Regards

  8. #8
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,574
    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., that's extremely confusing me. I think you are also little bit confuse because of my table. here you go. if the table being like this
    2019/06/05
    2019/06/07
    2019/06/10
    2019/06/13
    Missing Days without weekend 3
    Missing Days with weekend 5


    Kind Regards
    I don't know what's confusing you. To repeat :
    • 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)

  9. #9
    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

    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

  10. #10
    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

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

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
  •