Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF statement with two dates
I am trying to write an IF statement to tell me which fiscal year the
transcation occurred in. My formula is: =IF((AND(I2="11/1/2004",I2<="10/31/2005")),"FY 2005",(IF((AND(I2="11/1/2005",I2<="10/31/2006")),"FY 2006", "ERROR"))) I2 is the cell which contains the date of the transcation. Dates between 11/1/2004 and 10/31/2005 are in FY 2005 and dates between 11/1/2005 adn 10/31/2006 are in FY 2006. I appreciate any help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF statement with two dates
You need to use the DATE() function to express the dates:
=IF((AND(I2=DATE(2004,11,1),I2<=DATE(2005,10,31)) ,"FY 2005"... etc If the rule is that the fiscal year always runs from 1 Nov to 31 Oct, then a general formula with for the fiscal year is: =YEAR(I2)+IF(MONTH(I2)10,1,0) jbormann wrote: I am trying to write an IF statement to tell me which fiscal year the transcation occurred in. My formula is: =IF((AND(I2="11/1/2004",I2<="10/31/2005")),"FY 2005",(IF((AND(I2="11/1/2005",I2<="10/31/2006")),"FY 2006", "ERROR"))) I2 is the cell which contains the date of the transcation. Dates between 11/1/2004 and 10/31/2005 are in FY 2005 and dates between 11/1/2005 adn 10/31/2006 are in FY 2006. I appreciate any help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF statement with two dates
Would the formula work for you as =IF(AND(I2DATE(2004,10,31),I2<DATE(2005,11,1)),"F Y 2005",IF(AND(I2DATE(2005,10,31),I2<DATE(2006,11,1 )),"FY 2006","Error")) Hope that helps Carl jbormann Wrote: I am trying to write an IF statement to tell me which fiscal year the transcation occurred in. My formula is: =IF((AND(I2="11/1/2004",I2<="10/31/2005")),"FY 2005",(IF((AND(I2="11/1/2005",I2<="10/31/2006")),"FY 2006", "ERROR"))) I2 is the cell which contains the date of the transcation. Dates between 11/1/2004 and 10/31/2005 are in FY 2005 and dates between 11/1/2005 adn 10/31/2006 are in FY 2006. I appreciate any help. -- mr_teacher ------------------------------------------------------------------------ mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352 View this thread: http://www.excelforum.com/showthread...hreadid=562515 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating number of three month periods between two dates... | Excel Discussion (Misc queries) | |||
working out quarters (three-month periods) between two dates | Excel Worksheet Functions | |||
formula to add dates. | Excel Worksheet Functions | |||
Pre-1900 dates | Excel Discussion (Misc queries) | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) |