complex if statements in excel

[Julieeeee]

I need help with if statements.
I have two date columns - column A is date in fleet and column B is date out
of fleet. I want to find out how many fleet days I have in any given month.
Examples:
In Out #days in October'04
9/17/03 9/30/04 0
9/17/03 10/15/04 15
9/17/03 31 (where date out is blank)
11/30/04 3/31/05 0
11/30/04 0
10/5/04 27

Is it possible to do one if statement for two columns at once? I have tried
several times and keep getting "FALSE" for an answer.

Thanks!

Julie

 

[JE McGimpsey]

One way (though there's got to be a better way):

Enter your start date (e.g., 10/1/2004) in E1. The total days in October
2004 will then be (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(IF(B2:B7="", DATE(YEAR(E1),MONTH(E1)+1,1), IF(B2:B7<E1,E1,
IF(B2:B7>DATE(YEAR(E1),MONTH(E1)+1,1), DATE(YEAR(E1),MONTH(E1)+1,1),
B2:B7+1))), -IF(A2:A7<E1, E1, IF(A2:A7>DATE(YEAR(E1), MONTH(E1)+1,1),
DATE(YEAR(E1),MONTH(E1)+1,1), A2:A7)))

 

[bj]

try naming two cells as "start" and "end" and entering your start and end
date of interest. format these cells as dates
entering in your coulumn of interest
=min(0,if(or(out="",out>end),end,out)-max(start,in)

 

[Julieeeee]

Thank you so much for your response.
That's a heck-of-a formula! It seems to work with the following exceptions:

INF OOF 10/01/04
05/14/04 11/01/04 32
10/02/04 01/19/05 30
10/21/04 11

The days in fleet for the above should be 31, 2 and 21 respectively. Can
you tweak the formula to calculate those correctly??

 

[Julieeeee]

Please ignore my last reply. I meant to say your formula works except for:

INF OOF 10/01/04
05/14/04 11/01/04 32
10/02/04 01/19/05 30
10/21/04 11

In the above example only the first one is wrong - that should read 31 days,
not 32. Can you tweak the formula to calculate that correctly?

Thanks!

 

[JE McGimpsey]

Forgot an = sign:

=SUM(IF(B2:B7="", DATE(YEAR(E1),MONTH(E1)+1,1), IF(B2:B7<=E1,E1,
IF(B2:B7>DATE(YEAR(E1),MONTH(E1)+1,1), DATE(YEAR(E1),MONTH(E1)+1,1),
B2:B7+1))), -IF(A2:A7<E1, E1, IF(A2:A7>DATE(YEAR(E1), MONTH(E1)+1,1),
DATE(YEAR(E1),MONTH(E1)+1,1), A2:A7)))