Formulas
 

I'm trying to create a formula to minus one date from the next. So i've done
the standard equation in column d (=c1-b1) and i get my answer. but in some
cases some cells in column b are not filled in. so in column d i get these
really big numbers. i dont want those numbers there so i was wondering how i
get rid of them. also these two things im minusing are dates. i was wondering
if there was any ways to create the formula to only include work week days so
if cell b1 is friday january 4 2008 and c1 is monday january 7 2008. i want
cell d1 to say 1 and not 3. is this possible.

I hope someone can understand this. And any help would be much appreciated.

Formulas
 

if the analysis toolpak is installed, try:
=IF(B1,NETWORKDAYS(B1,C1)-1,"")


"Stephanie" wrote:

I'm trying to create a formula to minus one date from the next. So i've done
the standard equation in column d (=c1-b1) and i get my answer. but in some
cases some cells in column b are not filled in. so in column d i get these
really big numbers. i dont want those numbers there so i was wondering how i
get rid of them. also these two things im minusing are dates. i was wondering
if there was any ways to create the formula to only include work week days so
if cell b1 is friday january 4 2008 and c1 is monday january 7 2008. i want
cell d1 to say 1 and not 3. is this possible.

I hope someone can understand this. And any help would be much appreciated.

Formulas
 

C1 is January 7, 2008

B1 is January 4, 2008

=IF(B1="","",C1-B1) for the first part returns 3

For the second part you could use the NETWORKDAYS function from the Analysis
Toolpak Add-in.

=IF(B1="","",NETWORKDAYS(B1,C1))

returns 2 because Friday is a workday and Monday is a workday and NWD function
counts both.

NOTE: you must load the ATP through ToolsAdd-ins.

BTW.........the big numbers are just the serial numbers that Excel uses to keep
track of dates/times.


Gord Dibben MS Excel MVP


On Sun, 2 Mar 2008 14:37:01 -0800, Stephanie
wrote:

I'm trying to create a formula to minus one date from the next. So i've done
the standard equation in column d (=c1-b1) and i get my answer. but in some
cases some cells in column b are not filled in. so in column d i get these
really big numbers. i dont want those numbers there so i was wondering how i
get rid of them. also these two things im minusing are dates. i was wondering
if there was any ways to create the formula to only include work week days so
if cell b1 is friday january 4 2008 and c1 is monday january 7 2008. i want
cell d1 to say 1 and not 3. is this possible.

I hope someone can understand this. And any help would be much appreciated.

Formulas
 

I need help with something similar. I need to calculate turn around time by
number of days D2 being the start and W2 being the end date, with out the
lasrge numbers when there is no date. I have to gather these totals days from
several worksheets and calcualte their total on another spreadsheet Sheet!5.
Any help would be appreciated.

"JMB" wrote:

if the analysis toolpak is installed, try:
=IF(B1,NETWORKDAYS(B1,C1)-1,"")


"Stephanie" wrote:

I'm trying to create a formula to minus one date from the next. So i've done
the standard equation in column d (=c1-b1) and i get my answer. but in some
cases some cells in column b are not filled in. so in column d i get these
really big numbers. i dont want those numbers there so i was wondering how i
get rid of them. also these two things im minusing are dates. i was wondering
if there was any ways to create the formula to only include work week days so
if cell b1 is friday january 4 2008 and c1 is monday january 7 2008. i want
cell d1 to say 1 and not 3. is this possible.

I hope someone can understand this. And any help would be much appreciated.