Results 1 to 3 of 3
Thread: Old formula Help needed (2003)

20080313, 20:42 #1
 Join Date
 May 2002
 Location
 US
 Posts
 439
 Thanks
 0
 Thanked 0 Times in 0 Posts
Old formula Help needed (2003)
Greetings,
History: about 12 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 = 02112005, cell GR2 = 02082005
Using this first formula to get the Total number of days regardless of weekends, holidays
=IF(EF2="",TODAY()GR2,EF2GR2)
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

20080313, 21:58 #2
 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 118 = 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 EF2GF2+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

20080313, 22:08 #3
 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