Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Networkdays and Today()

    I enter todays date in cell A1 and tommorow's date in B1. I get the following results:
    Networkdays(A1,B1) = 1;
    Networkdays(today(),B1) = 2;
    B1-today() = 1.
    I think there is something I should know about using today() with the networkdays function. Also, for EXCEL trivia of the week, does anyone know why there are two Networkdays functions, one in UC and one in LC?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Networkdays and Today()

    I think what this is telling you is that there are 2 workdays in the period you are referring to. TODAY() is probably a working day and so is tommorow.

    The NETWORKDAYS function is part of Analysis ToolPak add-in and the Networkdays function is the VBA equivalent.

    Hope that clarifies

    Andrew C

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    St. George, Maine, USA
    Posts
    158
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Networkdays and Today()

    But when I enter today's date in cell A1 and tommorow's date in B1. I get the following results:
    Networkdays(A1,B1) = 2;
    Networkdays(today(),B1) = 2;



    Triva answer: Same as why there is Weeknum & WEEKNUM,
    Workday & WORKDAY and Yearfrac & YEARFRAC.

    Probably only Bill knows.

    Bob_D

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Networkdays and Today()

    Just for the record, NETWORKDAYS(TODAY(),TODAY()+1) = 2.

    Re Trivia, if you goto Tools|Add-ins you will see Analysis Toolpak checked and also Analysis Toolpak - VBA.

    AC

  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Networkdays and Today()

    As Networkdays returns integers only, one second to midnight to one second after midnight results in 2 workdays....

    If you are interested in a formula which gives you the networkdays in fraction of a day, I can post it here, however you must wait until www.experts-exchange.com is online again (sigh...), looks like I do not have the final version on my PC.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Networkdays and Today()

    As I understand it NETWORKDAYS() is not really a measure of time, but an indicator to whether a day is a "workday" or not, and using that to count the work days in a given period - a day is either a workday or it is not.

    Any of the above formulae could return 0 instead of 2 if entered into a worksheet on a Saturday, or 1 if entered on a Sunday.

    I suppose a use could be found for a version that returns fractions.

    Andrew C

  7. #7
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Networkdays and Today()

    With A4 containing the start date and N4 the end date:

    =IF(INT(N4)>INT(A4),NETWORKDAYS(A4,A4)*(1-(A4-INT(A4)))+IF(INT(N4)-INT(A4)>=2,NETWORKDAYS(A4+1,N4-1),0)+NETWORKDAYS(N4,N4)*(N4-INT(N4)),IF(INT(N4)=INT(A4),(N4-A4)*NETWORKDAYS(A4,N4),"Time Travel ?"))

    As for the format: The result are decimal (work)days. Therefore to:
    a) Transform to decimal hours: Multiply formula by 24
    [img]/forums/images/smilies/cool.gif[/img] Display total elapsed hours: Use custom format [h]:mm
    c) Make text string: As [D] hh:mm does not seem to be possible write in a separate cell
    =INT(O4) &" workday(s) and " &TEXT(O4-INT(O4),"hh:mm") &" hours"
    (assuming formula in O4)

Posting Permissions

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