+ Reply to Thread
Results 1 to 12 of 12

Excel counting weekends in Formula

  1. #1
    Registered User
    Join Date
    03-31-2015
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    8

    Smile Excel counting weekends in Formula

    Hello Everyone ,

    I am the new user in this forum and glad to see every solutions .

    I have one Google online spreadsheet where I have one simple formula which shows the no of production days and it is given as below ,

    =today()-H6

    It calculates the no of days as per the date entered in column (H) . My Problem is that it is counting Saturday and Sunday as well in this formula .
    Where we do not work on Weekends . I need the formula in a way that it should not count the weekends in the formula and should show the exact no of days . i.e If I have entered date "03/27/2015" which is Friday then it should not count weekends.

    Please help me with this formula .I really appreciate your help in advance .

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Excel counting weekends in Formula

    But not sure if mean Google Online or Excel Online.. Excel Online will support this

    =NETWORKDAYS.INTL(H6,TODAY(),1)
    Last edited by NeedForExcel; 03-31-2015 at 05:38 AM.
    Cheers!
    Deep Dave

  3. #3
    Registered User
    Join Date
    03-31-2015
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    8

    Re: Excel counting weekends in Formula

    Perfect and it is working well . Thanks so much .

    It would be really great if you could help me with one more problem.

    I have another excel with the same formula where I dont want formula counting Sundays. Also it should not count the same day when entered the formula .ie

    I have entered formula today then it should not count today and will show "0" in the result as well as it wont count Sundays .

    Great Thanks for your help .

  4. #4
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Excel counting weekends in Formula

    The above formula ignores Saturdays & Sundays aswell..

    For not counting today, you can try Today()-1 instead of just Today().

    Please let me know if that works, and that what you want..

  5. #5
    Registered User
    Join Date
    03-31-2015
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    8

    Re: Excel counting weekends in Formula

    Thanks so much and yes it is working perfectly well .

    Is this possible to count Saturday and only discount Sunday in the formula . Please suggest .Thank Again .

  6. #6
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Excel counting weekends in Formula

    That is possible.. instead of =NETWORKDAYS.INTL(H6,TODAY(),1) use =NETWORKDAYS.INTL(H6,TODAY(),11)

  7. #7
    Registered User
    Join Date
    03-31-2015
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    8

    Re: Excel counting weekends in Formula

    Thanks So much and it is working perfectly .

    I am so glad to find the site . Thanks Again .

  8. #8
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Excel counting weekends in Formula

    Quote Originally Posted by mdAzim View Post
    I am so glad to find the site . Thanks Again .
    So am I, and I am glad it helps..

  9. #9
    Registered User
    Join Date
    03-31-2015
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    8

    Re: Excel counting weekends in Formula

    Hello ,

    Hope everyone is doing good .

    I have been using this formula however I have noticed that no of days increased after 12:30 PM everyday .

    As per formula it is showing correct no of days before 12:30 PM But after this it increased .

    Please help me as I am using IST and my timezone is ( UTC + 5:30 ) Chennai ,Kolkata, Mumbai, New Delhi .

  10. #10
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Excel counting weekends in Formula

    If you are using TODAY() function in the formula it should only change at midnight according to your computer's settings, which formula are you using, exactly?
    Audere est facere

  11. #11
    Registered User
    Join Date
    03-31-2015
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    8

    Re: Excel counting weekends in Formula

    Thanks for the prompt reply and I am using below formula

    =NETWORKDAYS.INTL(H6,TODAY(),11)

  12. #12
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Excel counting weekends in Formula

    Quote Originally Posted by mdAzim View Post
    I have been using this formula however I have noticed that no of days increased after 12:30 PM everyday
    Don't think that should happen!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 11-01-2012, 03:41 PM
  2. Counting Formula (Count Weekends Off)
    By uhcord98 in forum Excel General
    Replies: 5
    Last Post: 09-19-2011, 05:06 PM
  3. Counting Weekends Off
    By uhcord98 in forum Excel General
    Replies: 3
    Last Post: 08-10-2011, 10:27 PM
  4. Exclude weekends in date counting
    By Ramzes in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-10-2009, 08:57 AM
  5. [SOLVED] How to Calculate Dates without counting the weekends
    By Lillian F in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-24-2005, 06:07 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1