Prev: Search Capability
Next: columns
From: HFST04 on 23 Apr 2010 15:11
I need a formula that calculates the working days (A) from start to todays
date and (B) from start to completion. I also don't want a value displayed
until at least one date is entered so it doesn't corrupt my avg formula.
I'm currently using =IF(AND(D3="",C3=""),"",IF(D3="",TODAY()-C3,D3-C3)) in
column C and it returns the values listed below.

I want it to return only working days.
A B C
4/15/10 4/23/10 8
4/20/10 3

Thanks

From: Luke M on 23 Apr 2010 15:40
For an inclusive count (counts both the starting date and finish date as a
workday)
=IF(A4="","",NETWORKDAYS(A4,IF(B4="",TODAY(),B4)))

Exclusive count:
=IF(A4="","",NETWORKDAYS(A4,IF(B4="",TODAY(),B4))-1)

Note that the NETWORKDAYS function is part ot the Analysis ToolPak add-in.
Also, if you want, that function supports the use of using a range to
declare holidays (days that you don't want to count as workdays)

--
Best Regards,

Luke M
"HFST04" <HFST04(a)discussions.microsoft.com> wrote in message
news:B5DACD37-248B-406E-B35B-A916A0702273(a)microsoft.com...
>I need a formula that calculates the working days (A) from start to todays
> date and (B) from start to completion. I also don't want a value displayed
> until at least one date is entered so it doesn't corrupt my avg formula.
> I'm currently using =IF(AND(D3="",C3=""),"",IF(D3="",TODAY()-C3,D3-C3)) in
> column C and it returns the values listed below.
>
> I want it to return only working days.
> A B C
> 4/15/10 4/23/10 8
> 4/20/10 3
>
> Thanks
>


From: JLatham on 23 Apr 2010 16:23
Check Excel Help for worksheet function NETWORKDAYS(). It will even allow
you to set up a list of holidays and exclude them from the count of days
along with excludingn weekends.
Note that this function is part of the Analysis ToolPak add-in, so you'll
need to 'install' that add-in before you can actually use the function. Look
under Tools --> Add-ins in pre-2007 Excel,


"HFST04" wrote:

> I need a formula that calculates the working days (A) from start to todays
> date and (B) from start to completion. I also don't want a value displayed
> until at least one date is entered so it doesn't corrupt my avg formula.
> I'm currently using =IF(AND(D3="",C3=""),"",IF(D3="",TODAY()-C3,D3-C3)) in
> column C and it returns the values listed below.
>
> I want it to return only working days.
> A B C
> 4/15/10 4/23/10 8
> 4/20/10 3
>
> Thanks
>
 | 
Pages: 1
Prev: Search Capability
Next: columns