| 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 . . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Function with Dates
There's no reason that formula shouldn't work. What is in A1 and B1?
Regards, Fred "Bagia" wrote in message ... 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 . . |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Function with Dates
Instead of retyping the formula, copy it from the formula bar and paste it into
your message. It's too easy to make a typing error -- even one that corrects the actual error in your message! Bagia wrote: 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 . . -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Function with Dates
Suppose Start date is in A1, end date is in B1 and FY start date is in C1
and FY end date is in D1, Then put this formula in C1 =IF(OR(AND(D1=A1,D1<=B1),AND(E1=A1,E1<=B1)),1,0) Vijay "Bagia" wrote: 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 . . |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Function with Dates
"Vijay" wrote:
Sorry, pl read it like this, Suppose Start date is in A1, end date is in B1 and FY start date is in D1 and FY end date is in E1, Then put this formula in C1 =IF(OR(AND(D1=A1,D1<=B1),AND(E1=A1,E1<=B1)),1,0) Vijay "Vijay" wrote: Suppose Start date is in A1, end date is in B1 and FY start date is in C1 and FY end date is in D1, Then put this formula in C1 =IF(OR(AND(D1=A1,D1<=B1),AND(E1=A1,E1<=B1)),1,0) Vijay "Bagia" wrote: 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) | |||
Linear Mode
