Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Old formula Help needed (2003)

    Greetings,

    History: about 1-2 years ago, I had help (from this forum) on a formula to handle calculating the actual number of workdays between dates. Now I think I understand what is happening with this formula, but I question the results.

    as an example: cell EF2 = 02-11-2005, cell GR2 = 02-08-2005

    Using this first formula to get the Total number of days regardless of weekends, holidays
    =IF(EF2="",TODAY()-GR2,EF2-GR2)

    Results in 3

    Now if I compare to the formula results for the workdays:

    =IF(NETWORKDAYS(GR20,EF20,Calendar!$E$1:$E$277)<0, TODAY()-GR20,IF(EF20>0,1+NETWORKDAYS(GR20,EF20,Calendar!$E $1:$E$277),(IF(EF<0,1+NETWORDAYS(GR20,EF20,Calenda r!$E$1:$E$277)))))

    Results in 5



    Feb 8 2005 is a Tuesday Feb 11 2005 is a Friday. For the data in the Calendar page, I do not have any holidays listed, nor is there a weekend between the dates.

    2 Questions:

    1) how come the formula is returning a count of 5?

    2) If the date in EF2 is blank, how can I still see the number of actual workdays (minus holidays and weekends)?

    Regards,
    Brad

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Old formula Help needed (2003)

    The first formula results in 3 since 11-8 = 3

    The second formula results in 5 since (assuming there is no holidays in the list)

    =1+NETWORKDAYS(GR20,EF20,Calendar!$E$1:$E$277)
    = 1+4 = 5

    The network days = 4 since 8,9,10,11 (Tues - Fri) are all weekdays.

    The number of days should be 4 so I don't know why you add 1 to the networkdays. you must add 1 to the difference EF2-GF2+1 = 4 since to get the correct count

    Not sure I understand: If the date in EF2 is blank what are you comparing GF2 to to make the difference? If there is only GF2 then the answer is 1

    Steve

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Old formula Help needed (2003)

    steve,
    Yea, I am not sure why the '1' is in the formula. It has been too long, and I do not remember the orginal post.

    For the second question, As it stands, the formula will return the same number of days open as the first formula until there is a Date in the EF cell. I am understanding a little more for the formula. There must be 2 dates to get the comparision to function.



    What I am trying to accomplish is to see the number of working days a item is open (as it is in a state of open). The second result is desired as well, knowing the number of working days After it becomes closed (IE: the EF cell). Not sure if this can be accomplished.




    Thanks for the info/help,

    Brad

Posting Permissions

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