Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF and AND formula for date field
Hello:
I have a column that has dates in it in the format of "MM/DD/YY". I'd like to create a formula that would evaluate this column and return a value based on the following conditions. If, lets say "B2" (the date) is less than or equal to DATEVALUE("01/02/09") and more or equal to DATEVALUE("01/01/10") return the year value of teh "B2" cell. Not sure if there is a way but the rest of the dates fall withing 2009 for which I need to return the year and quarter...ie. 2009 Q1; 2009 Q2 thorugh Q4. It might not work all within one formula but as far as I get the first part that would help. I can just manually do the 2009 then. Thank you. Monika |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF and AND formula for date field
Try this:
=IF(B2<DATEVALUE("01/02/2009"),YEAR(B2),IF(B2DATEVALUE ("01/01/2010"),YEAR(B2),"Q"&(INT(MONTH(B2)/4)+1))) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF and AND formula for date field
Hello:
didn't work, it didn't give me any error just showed the formula as typed in... Please advise. Thank you. Monika "jasontferrell" wrote: Try this: =IF(B2<DATEVALUE("01/02/2009"),YEAR(B2),IF(B2DATEVALUE ("01/01/2010"),YEAR(B2),"Q"&(INT(MONTH(B2)/4)+1))) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF and AND formula for date field
Did you put an equal sign in front of the formula? Is the cell formatted for
text? Either of these would cause the problem you described. It works for me when I test it, so the problem has to be operator error. Regards, Fred "murkaboris" wrote in message ... Hello: didn't work, it didn't give me any error just showed the formula as typed in... Please advise. Thank you. Monika "jasontferrell" wrote: Try this: =IF(B2<DATEVALUE("01/02/2009"),YEAR(B2),IF(B2DATEVALUE ("01/01/2010"),YEAR(B2),"Q"&(INT(MONTH(B2)/4)+1))) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF and AND formula for date field
Sure was me, the whole column was formatted as text.
Thank you. One more adjustment if its possible, for 2009 it returns Q2, Q3, Q4 instead of 2009 Q2, 2009 Q3...is there a way to adjust the last leg to return the year and the quarter instead of just the quarter? Please advise. Thank you. Monika "Fred Smith" wrote: Did you put an equal sign in front of the formula? Is the cell formatted for text? Either of these would cause the problem you described. It works for me when I test it, so the problem has to be operator error. Regards, Fred "murkaboris" wrote in message ... Hello: didn't work, it didn't give me any error just showed the formula as typed in... Please advise. Thank you. Monika "jasontferrell" wrote: Try this: =IF(B2<DATEVALUE("01/02/2009"),YEAR(B2),IF(B2DATEVALUE ("01/01/2010"),YEAR(B2),"Q"&(INT(MONTH(B2)/4)+1))) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF and AND formula for date field
Yes, this will do it.
=IF(B2<DATEVALUE("01/02/2009"),YEAR(B2),IF(B2DATEVALUE ("01/01/2010"),YEAR(B2),"2009Q"&(INT(MONTH(B2)/4)+1))) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF and AND formula for date field
Absolutely wonderful!
Thank you, you have saved me soooo much time :). Monika "jasontferrell" wrote: Yes, this will do it. =IF(B2<DATEVALUE("01/02/2009"),YEAR(B2),IF(B2DATEVALUE ("01/01/2010"),YEAR(B2),"2009Q"&(INT(MONTH(B2)/4)+1))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need formula to subtract 5hrs from date/time field | Excel Discussion (Misc queries) | |||
Formula to add 12 months to an existing date field | Excel Discussion (Misc queries) | |||
New date based on one date field minus minutes in another field | Excel Discussion (Misc queries) | |||
Formula for date field | New Users to Excel | |||
Linked date field in worksheet defaults a blank field as 1/0/1900 | Excel Worksheet Functions |