Date Counting problem

S

Sandy

Hi wondering if I can get any can help solve my problem, I have 2 column that
contains dates as shown below :

A B C D
(Reference cell)
1 01/01/2001 25/01/2001 24 =TODAY()
2 01/02/2001 0 ??

I have tried (without much success) using networkdays, count's etc etc.

Thanks in advance for any help/suggestions (except the rude ones ??)
 
H

henry

Maybe days360 might help, format the result C cell as number

D cell, what are you looking for?
 
S

Sandy

Hi there, D is the reference cell, and the result should only be in C this
result should be the number of days between the date in A1 and B1 (Full
dates), and in this case A2 (Full Date) and B2 where there is a 0 value as no
date is available at this time.

Thanks
 
D

Duke Carey

Sandy-

In col C use

=B1-A1

Format the cell as General or Comma or the like, otherwise Excel makes it a
date.

Your statement about row 2 isn't clear, so if you want NO RESULT when B2 is
zero, use

=if(B2=0,0,B2-a2)

otherwise you'll get a huge number when B2 is zero or empty
 
S

Sandy

Hi Duke,

Agree with your comments and solutions as this is what i have tried, the
problem that i am trying to solve is that in row 2 where there is a 0 in B2
then i want the calculation to be made ignoring B2 and referencing D1 which
has the =TODAY() formula thus giving an answer, ie if D1 had a date of
01/02/2006 the number of days that no action has been taken (shown in C2)
would have been approx 1800 days.

Thanks
 
D

Duke Carey

Then use

=if(B2=0,today()-A2,B2-a2)

Sandy said:
Hi Duke,

Agree with your comments and solutions as this is what i have tried, the
problem that i am trying to solve is that in row 2 where there is a 0 in B2
then i want the calculation to be made ignoring B2 and referencing D1 which
has the =TODAY() formula thus giving an answer, ie if D1 had a date of
01/02/2006 the number of days that no action has been taken (shown in C2)
would have been approx 1800 days.

Thanks
Click to expand...
 
B

Brent

Hi Duke,

I found this very helpful, however how I include the Networkdays function in
this formula? I need the same basics as =if(B2=0,today()-A2,B2-a2) however I
need to exclude weekends.

Any ideas?

Thanks,

Brent
 
D

David Biddulph

Sorry to come back with the old RTFM answer, but if you type NETWORKDAYS
into Excel's help, it will tell you the syntax of the function, and give you
examples.
 
Top