I am trying to build a spread sheet to help me check our billing software. I
would like to do this once a month, at the end of the month. I can input the
last day of the month in cell H2.


Is it possible to build a formula for Col G that can calculate the business
days as shown in this table ?

A B C D E F G

Service Business
Customer Start FreeStart FreeEnd ServiceStop Discontinued Days to
Bill
ABC 6/15/04 11/15/04 12/6/04 20
ABC 6/15/04 12/16/04 12
ABC 6/15/04 7/15/04 0
ABC 6/15/04 23
ABC 6/15/04 12/18/04 1/17/04 18
ABC 6/15/04 11/15/04 1/17/04 0
ABC TBD 0
ABC 6/15/04 9/15/04 0
ABC 6/15/04 9/15/04 0

Thank you in advance.

Look at the NETWORKDAYS function, it is part of the ATP so it must be
installed (or you'll get a name error) but it comes with office/excel


=NETWORKDAYS(start_date,end_date,holidays_if_neede d)

will return the number of workdays


Regards,

Peo Sjoblom

"carl" wrote:

I am trying to build a spread sheet to help me check our billing software. I
would like to do this once a month, at the end of the month. I can input the
last day of the month in cell H2.


Is it possible to build a formula for Col G that can calculate the business
days as shown in this table ?

A B C D E F G

Service Business
Customer Start FreeStart FreeEnd ServiceStop Discontinued Days to
Bill
ABC 6/15/04 11/15/04 12/6/04 20
ABC 6/15/04 12/16/04 12
ABC 6/15/04 7/15/04 0
ABC 6/15/04 23
ABC 6/15/04 12/18/04 1/17/04 18
ABC 6/15/04 11/15/04 1/17/04 0
ABC TBD 0
ABC 6/15/04 9/15/04 0
ABC 6/15/04 9/15/04 0

Thank you in advance.

thanks peo. i am familiar with the function. my question is how to build the
"if" logic. the formula needs to look at dates in columns b,c,d,e,f inorder
to calculate the correct number of days. can i send you the spreadsheet to
take a look at ?

"Peo Sjoblom" wrote:

Look at the NETWORKDAYS function, it is part of the ATP so it must be
installed (or you'll get a name error) but it comes with office/excel


=NETWORKDAYS(start_date,end_date,holidays_if_neede d)

will return the number of workdays


Regards,

Peo Sjoblom

"carl" wrote:

I am trying to build a spread sheet to help me check our billing software. I
would like to do this once a month, at the end of the month. I can input the
last day of the month in cell H2.


Is it possible to build a formula for Col G that can calculate the business
days as shown in this table ?

A B C D E F G

Service Business
Customer Start FreeStart FreeEnd ServiceStop Discontinued Days to
Bill
ABC 6/15/04 11/15/04 12/6/04 20
ABC 6/15/04 12/16/04 12
ABC 6/15/04 7/15/04 0
ABC 6/15/04 23
ABC 6/15/04 12/18/04 1/17/04 18
ABC 6/15/04 11/15/04 1/17/04 0
ABC TBD 0
ABC 6/15/04 9/15/04 0
ABC 6/15/04 9/15/04 0

Thank you in advance.