complex if statements in excel

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

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)))


In article <[email protected]>,
Julieeeee <[email protected]> wrote:

> 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

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" wrote:

> 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
>

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??

"JE McGimpsey" wrote:

> 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)))
>
>
> In article <[email protected]>,
> Julieeeee <[email protected]> wrote:
>
> > 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
>

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" wrote:

> 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)))
>
>
> In article <[email protected]>,
> Julieeeee <[email protected]> wrote:
>
> > 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
>

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)))

In article <[email protected]>,
Julieeeee <[email protected]> wrote:

> 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?