Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Excel File in Use Notification Missing?? | Excel Discussion (Misc queries) | |||
Excel Files Acting Weird | Excel Discussion (Misc queries) | |||
I get a program error when I download an excel template | Excel Discussion (Misc queries) | |||
Excel error - Startup (and Acrobat PDFMaker) | Setting up and Configuration of Excel |