Need Formula

[George]

I need help with a formula for Cell E3 that will give me the total days
between cell C3 and D3 - Here's what I am trying to do.

Column C3 - Enter Deployment Start Date

Column D3 - Enter Deployment Return Date

Column E3 - Total Days Deployed

As of now I am using =SUM(D3-C3) but it does some strange things.
If only C3 or D3 are filled in it gives a bogus number until both cells are
filled
in. Also it always displays the number zero unless a value is entered - I
would rather the cell remain blank if cells C3 and D3 are blank.

Thanks

 

[Ron Coderre]

Try this:

E3: =IF(COUNT(C33)=2,D3-C3,"")

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Rick Rothstein \(MVP - VB\)]

You don't need the SUM function. Give this a try...

=IF(OR(C3="",D3=""),"",C3-D3)

Rick

 

[Rick Rothstein \(MVP - VB\)]

Damn! I have the D and C columns reversed...

=IF(OR(C3="",D3=""),"",D3-C3)

Rick

 

[George]

Ron - That is perfect Thanks

Can you answer one more thing ?

In my date cells I am I set the format to date - but is there a way
that if the date is entered without the slashes between the dd/mm/yyyy that
it will automatically do it ?

Thanks

 

[T. Valko]

See this:

http://www.cpearson.com/Excel/DateTimeEntry.htm

 

[Stan Brown]

Sat, 15 Dec 2007 20:19:00 -0800 from George

I need help with a formula for Cell E3 that will give me the total
days between cell C3 and D3 - Here's what I am trying to do.
Column C3 - Enter Deployment Start Date
Column D3 - Enter Deployment Return Date
Column E3 - Total Days Deployed
As of now I am using =SUM(D3-C3) but it does some strange things.

Lose the SUM function. You're doing a straight subtraction.

If only C3 or D3 are filled in it gives a bogus number until both
cells are filled in.

Well, of course. How could it be otherwise? But you can solve this
problem along with the other one you mentioned:

Also it always displays the number zero unless a value is entered - I
would rather the cell remain blank if cells C3 and D3 are blank.

=if(and(C3>0,D3>0),D3-C3,"")