Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Date Difference Without Weekends

  1. #1
    Join Date
    3rd December 2004
    Posts
    30

    Date Difference Without Weekends

    Good day,

    I have searched google and many forums and cannot find out what formula I can use to calculate the difference between two dates while not including weekends. For example in cell A1 I have 27-Feb-07 and in B1 I have 05-Mar-07 ; it is possible to to calculate the difference between these by not including weekends. The weekends I am refering to are Saturday and Sunday.

    Thank you for your help,

    Chris

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,525

    Re: Date Difference Without Weekends

    Yes... special function in Excel called:

    NETWORKDAYS

    use like =Networkdays(Start,Finish,Any Holidays)

    Check out the Excel Help on it for more information.

    You must add the "Analysis Tool Pak" to access this function (from the Tools/Add-in Manager).

    Ger
    Last edited by Ger Plante; March 15th, 2007 at 23:27.
    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  3. #3
    Join Date
    3rd December 2004
    Posts
    30

    Re: Date Difference Without Weekends

    Thank you for your respone.
    I enter =Networkdays(B135,C135) in the cell and received the #Name? message. Do you have to enter the "Any Holidays" section although there isn't any?

    Thank you,

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,525

    Re: Date Difference Without Weekends

    Like I said - you MUST have the Analysis Tool Pak added in...

    Go to Tools/Add-In and check to make sure "Analysis ToolPak" is there.

    Ger
    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  5. #5
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,525

    Re: Date Difference Without Weekends

    Check also to make sure the two cells that are references are valid dates and formatted as a date.

    Thanks,
    Ger
    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  6. #6
    Join Date
    3rd December 2004
    Posts
    30

    Re: Date Difference Without Weekends

    My bad, I did't have the add-in added.
    Sorry about that.
    If you have 27-Feb-07 in A1 and in B1 you have 05-Mar-07 , wouldn't the differene be 4, considering the DateDiff function between those dates are 6? Because the networkdays function is giving me 5.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,525

    Re: Date Difference Without Weekends

    Well... whats a day between friends

    Seriously, the Networkdays function counts the first day as a date...

    if start date and end date are the same (27 Feb), it will return 1, whereas you might expect it to be zero.

    In otherwords, its an inlclusive function.

    Ger
    Last edited by Ger Plante; March 15th, 2007 at 23:44.
    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  8. #8
    Join Date
    3rd December 2004
    Posts
    30

    Re: Date Difference Without Weekends

    Makes sense.

    Thank you.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 6
    Last Post: May 23rd, 2008, 08:27
  2. Time Difference Ignoring Weekends
    By krishr1 in forum EXCEL HELP
    Replies: 2
    Last Post: September 6th, 2007, 21:34
  3. Calculate Future Date Excluding Weekends
    By potain in forum EXCEL HELP
    Replies: 9
    Last Post: August 27th, 2007, 22:23
  4. Date Calculator For Weekends & Holidays
    By Newt in forum Excel and/or Access Help
    Replies: 10
    Last Post: September 27th, 2006, 03:53
  5. Replies: 4
    Last Post: July 11th, 2006, 07:36

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
  •  
porno