| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Function with Dates
Hello,
I hope someone can help me. I would like to create an IF function for dates in my worksheet. I have a Start Date column and End Date column. If the date in the Start Date and End Date falls within the Fiscal Year(06), then I want to display a 1, otherwise a 0(zero). A1=7/01/06 (Start Date); B1=6/30/10 (End Date) FY06 is from 7/1/2005 to 6/30/2006 What I have in C1 is =IF(AND(A1=7/1/2005,B1<=6/30/2006),1,0) My result for FY06 is 0, but that's not correct because the End Date is not until 12/31/2010, so it falls within the FY06. I want this formula to do the same for FY07, FY08, and FY09. Can someone help? Thanks in Advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Function with Dates
=IF(AND(A1=date(2005,7,1),B1<=date(2006,6,30)),1, 0)
or even =--(AND(A1=date(2005,7,1),B1<=date(2006,6,30))) or =(A1=date(2005,7,1))*(B1<=date(2006,6,30)) The first - changes true/false to -1/0. The second - changes it 1/0. Multiplication (true*true=1) does the same thing. Bagia wrote: Hello, I hope someone can help me. I would like to create an IF function for dates in my worksheet. I have a Start Date column and End Date column. If the date in the Start Date and End Date falls within the Fiscal Year(06), then I want to display a 1, otherwise a 0(zero). A1=7/01/06 (Start Date); B1=6/30/10 (End Date) FY06 is from 7/1/2005 to 6/30/2006 What I have in C1 is =IF(AND(A1=7/1/2005,B1<=6/30/2006),1,0) My result for FY06 is 0, but that's not correct because the End Date is not until 12/31/2010, so it falls within the FY06. I want this formula to do the same for FY07, FY08, and FY09. Can someone help? Thanks in Advance. -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Function with Dates
Hi Dave,
I used the first option and my result comes back with 0(zero) but it should be 1, because the end date is 06/30/2010 so the Fiscal Year for 06 falls within the Date Range. FY06 date range is from 7/1/2005 to 6/30/2006 FY07 ......7/1/2006 to 6/30/2007 FY08 ......7/1/2007 to 6/30/2008 FY09 ......7/1/2008 to 6/30/2009 FY06=1 FY07=1 FY08=1 FY09=1 I also have A2=09/12/05 (start date) B2=06/30/06 (end date) So answer should be: FY06=1 FY07=0 FY08=0 FY09=0 "Dave Peterson" wrote: =IF(AND(A1=date(2005,7,1),B1<=date(2006,6,30)),1, 0) or even =--(AND(A1=date(2005,7,1),B1<=date(2006,6,30))) or =(A1=date(2005,7,1))*(B1<=date(2006,6,30)) The first - changes true/false to -1/0. The second - changes it 1/0. Multiplication (true*true=1) does the same thing. Bagia wrote: Hello, I hope someone can help me. I would like to create an IF function for dates in my worksheet. I have a Start Date column and End Date column. If the date in the Start Date and End Date falls within the Fiscal Year(06), then I want to display a 1, otherwise a 0(zero). A1=7/01/06 (Start Date); B1=6/30/10 (End Date) FY06 is from 7/1/2005 to 6/30/2006 What I have in C1 is =IF(AND(A1=7/1/2005,B1<=6/30/2006),1,0) My result for FY06 is 0, but that's not correct because the End Date is not until 12/31/2010, so it falls within the FY06. I want this formula to do the same for FY07, FY08, and FY09. Can someone help? Thanks in Advance. -- Dave Peterson . |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Function with Dates
My goal to display a one if the Fiscal Year falls within the Start Date and
End Date, then it should have a 1, otherwise the result should be 0. So if my start date is 10/15/07 and end date is 07/01/08, my result should be: FY06=0 FY07=0 FY08=1 FY09=1 Appreciate all the help! "Bagia" wrote: Hi Dave, I used the first option and my result comes back with 0(zero) but it should be 1, because the end date is 06/30/2010 so the Fiscal Year for 06 falls within the Date Range. FY06 date range is from 7/1/2005 to 6/30/2006 FY07 ......7/1/2006 to 6/30/2007 FY08 ......7/1/2007 to 6/30/2008 FY09 ......7/1/2008 to 6/30/2009 FY06=1 FY07=1 FY08=1 FY09=1 I also have A2=09/12/05 (start date) B2=06/30/06 (end date) So answer should be: FY06=1 FY07=0 FY08=0 FY09=0 "Dave Peterson" wrote: =IF(AND(A1=date(2005,7,1),B1<=date(2006,6,30)),1, 0) or even =--(AND(A1=date(2005,7,1),B1<=date(2006,6,30))) or =(A1=date(2005,7,1))*(B1<=date(2006,6,30)) The first - changes true/false to -1/0. The second - changes it 1/0. Multiplication (true*true=1) does the same thing. Bagia wrote: Hello, I hope someone can help me. I would like to create an IF function for dates in my worksheet. I have a Start Date column and End Date column. If the date in the Start Date and End Date falls within the Fiscal Year(06), then I want to display a 1, otherwise a 0(zero). A1=7/01/06 (Start Date); B1=6/30/10 (End Date) FY06 is from 7/1/2005 to 6/30/2006 What I have in C1 is =IF(AND(A1=7/1/2005,B1<=6/30/2006),1,0) My result for FY06 is 0, but that's not correct because the End Date is not until 12/31/2010, so it falls within the FY06. I want this formula to do the same for FY07, FY08, and FY09. Can someone help? Thanks in Advance. -- Dave Peterson . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Function with Dates
Show us the formula you used.
Regards, Fred "Bagia" wrote in message ... My goal to display a one if the Fiscal Year falls within the Start Date and End Date, then it should have a 1, otherwise the result should be 0. So if my start date is 10/15/07 and end date is 07/01/08, my result should be: FY06=0 FY07=0 FY08=1 FY09=1 Appreciate all the help! "Bagia" wrote: Hi Dave, I used the first option and my result comes back with 0(zero) but it should be 1, because the end date is 06/30/2010 so the Fiscal Year for 06 falls within the Date Range. FY06 date range is from 7/1/2005 to 6/30/2006 FY07 ......7/1/2006 to 6/30/2007 FY08 ......7/1/2007 to 6/30/2008 FY09 ......7/1/2008 to 6/30/2009 FY06=1 FY07=1 FY08=1 FY09=1 I also have A2=09/12/05 (start date) B2=06/30/06 (end date) So answer should be: FY06=1 FY07=0 FY08=0 FY09=0 "Dave Peterson" wrote: =IF(AND(A1=date(2005,7,1),B1<=date(2006,6,30)),1, 0) or even =--(AND(A1=date(2005,7,1),B1<=date(2006,6,30))) or =(A1=date(2005,7,1))*(B1<=date(2006,6,30)) The first - changes true/false to -1/0. The second - changes it 1/0. Multiplication (true*true=1) does the same thing. Bagia wrote: Hello, I hope someone can help me. I would like to create an IF function for dates in my worksheet. I have a Start Date column and End Date column. If the date in the Start Date and End Date falls within the Fiscal Year(06), then I want to display a 1, otherwise a 0(zero). A1=7/01/06 (Start Date); B1=6/30/10 (End Date) FY06 is from 7/1/2005 to 6/30/2006 What I have in C1 is =IF(AND(A1=7/1/2005,B1<=6/30/2006),1,0) My result for FY06 is 0, but that's not correct because the End Date is not until 12/31/2010, so it falls within the FY06. I want this formula to do the same for FY07, FY08, and FY09. Can someone help? Thanks in Advance. -- Dave Peterson . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Function with Dates
Thanks your email Fred. Below is the formula I used
=IF(AND(A1=date(2005,7,1),B1<=date(2006,6,30)),1, 0) "Fred Smith" wrote: Show us the formula you used. Regards, Fred "Bagia" wrote in message ... My goal to display a one if the Fiscal Year falls within the Start Date and End Date, then it should have a 1, otherwise the result should be 0. So if my start date is 10/15/07 and end date is 07/01/08, my result should be: FY06=0 FY07=0 FY08=1 FY09=1 Appreciate all the help! "Bagia" wrote: Hi Dave, I used the first option and my result comes back with 0(zero) but it should be 1, because the end date is 06/30/2010 so the Fiscal Year for 06 falls within the Date Range. FY06 date range is from 7/1/2005 to 6/30/2006 FY07 ......7/1/2006 to 6/30/2007 FY08 ......7/1/2007 to 6/30/2008 FY09 ......7/1/2008 to 6/30/2009 FY06=1 FY07=1 FY08=1 FY09=1 I also have A2=09/12/05 (start date) B2=06/30/06 (end date) So answer should be: FY06=1 FY07=0 FY08=0 FY09=0 "Dave Peterson" wrote: =IF(AND(A1=date(2005,7,1),B1<=date(2006,6,30)),1, 0) or even =--(AND(A1=date(2005,7,1),B1<=date(2006,6,30))) or =(A1=date(2005,7,1))*(B1<=date(2006,6,30)) The first - changes true/false to -1/0. The second - changes it 1/0. Multiplication (true*true=1) does the same thing. Bagia wrote: Hello, I hope someone can help me. I would like to create an IF function for dates in my worksheet. I have a Start Date column and End Date column. If the date in the Start Date and End Date falls within the Fiscal Year(06), then I want to display a 1, otherwise a 0(zero). A1=7/01/06 (Start Date); B1=6/30/10 (End Date) FY06 is from 7/1/2005 to 6/30/2006 What I have in C1 is =IF(AND(A1=7/1/2005,B1<=6/30/2006),1,0) My result for FY06 is 0, but that's not correct because the End Date is not until 12/31/2010, so it falls within the FY06. I want this formula to do the same for FY07, FY08, and FY09. Can someone help? Thanks in Advance. -- Dave Peterson . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using Dates with the IF function | Excel Discussion (Misc queries) | |||
| need a function (UDF) return X if 2 dates fall between 2 other dates | Excel Worksheet Functions | |||
| If Function with Dates | Excel Worksheet Functions | |||
| Using IF Function on Dates | Excel Discussion (Misc queries) | |||
| if function with dates | Excel Discussion (Misc queries) | |||
Hybrid Mode
