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:B7DATE(YEAR(E1),MONTH(E1)+1,1), DATE(YEAR(E1),MONTH(E1)+1,1),
B2:B7+1))), -IF(A2:A7<E1, E1, IF(A2:A7DATE(YEAR(E1), MONTH(E1)+1,1),
DATE(YEAR(E1),MONTH(E1)+1,1), A2:A7)))


In article ,
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

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="",outend),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:B7DATE(YEAR(E1),MONTH(E1)+1,1), DATE(YEAR(E1),MONTH(E1)+1,1),
B2:B7+1))), -IF(A2:A7<E1, E1, IF(A2:A7DATE(YEAR(E1), MONTH(E1)+1,1),
DATE(YEAR(E1),MONTH(E1)+1,1), A2:A7)))


In article ,
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

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:B7DATE(YEAR(E1),MONTH(E1)+1,1), DATE(YEAR(E1),MONTH(E1)+1,1),
B2:B7+1))), -IF(A2:A7<E1, E1, IF(A2:A7DATE(YEAR(E1), MONTH(E1)+1,1),
DATE(YEAR(E1),MONTH(E1)+1,1), A2:A7)))


In article ,
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

Forgot an = sign:

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

In article ,
Julieeeee 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?