Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Problem using IF statement with dates as part of the formula
Hello, I'm trying to figure out why my current formula is not coming up with the correct result. I have a list of dates in column A:
02/15/2013 01/05/2013 03/01/2013 etc. I would like column B to reference the corresponding cells in column A and populate the correct Quarter for which the date falls into (Qtr1, Qtr2,etc). My current formula looks like this: =IF(A1<4/1/2013,"Qtr1",IF(A1<7/1/2013,"Qtr2",IF(A1<10/1/2013,"Qtr3","Qtr4"))) The value that it gives me, regardless of the date in column A is Qtr4. I'm sure there is something obvious that I am doing incorrectly. Thanks in advance to anyone that can help! |
#2
|
|||
|
|||
Quote:
An easier way would be to replace the dates in your formula with the 5digit numbers that Excel uses to interpret dates or put the dates in cells elsewhere and refer to those. If you post an example workbook I'll happily show you how. It may be that there's actually a better way to accomplish this than with a nested IF. S. |
#3
|
|||
|
|||
Quote:
I'm going to try and attach a workbook with showing some dates and the way it's organized. Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem using IF statement with dates as part of the formula
Hi Izzy,
Am Thu, 7 Mar 2013 18:06:22 +0000 schrieb Izzy604: Hello, I'm trying to figure out why my current formula is not coming up with the correct result. I have a list of dates in column A: 02/15/2013 01/05/2013 03/01/2013 etc. I would like column B to reference the corresponding cells in column A and populate the correct Quarter for which the date falls into (Qtr1, Qtr2,etc). in B1 try: ="Qtr"&MONTH(MONTH(A1)&0) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem using IF statement with dates as part of the formula
"Izzy604" wrote:
My current formula looks like this: =IF(A1<4/1/2013,"Qtr1",IF(A1<7/1/2013,"Qtr2",IF(A1<10/1/2013,"Qtr3","Qtr4"))) Try: =IF(A1<--"4/1/2013","Qtr1",IF(A1<--"7/1/2013","Qtr2", IF(A1<--"10/1/2013","Qtr3","Qtr4"))) Without double-quotes, 4/1/2013 is interpreted as a numeric expression: 4 divided by 1 divided by 2013. But with double-quotes, "4/1/2013" is simply text, which Excel does not interpret. The double negative (--) or any arithmetic operation causes Excel to try to interpret the text as a numeric input, just as if you had typed into a cell. However, as a matter of "good practice", the following is better because it is independent of regional differences, which really matters only if you might share the Excel file with others: =IF(A1<DATE(2013,4,1),"Qtr1",IF(A1<DATE(2013,7,1), "Qtr2", IF(A1<DATE(2013,10,1),"Qtr3","Qtr4"))) Finally, note that your formula only works for 2013. More generally: =IF(MONTH(A1)<4,"Qtr1",IF(MONTH(A1)<7,"Qtr2",IF(MO NTH(A1)<10,"Qtr3","Qtr4"))) |
#6
|
|||
|
|||
Quote:
|
#7
|
|||
|
|||
Quote:
Thank you!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dates formula problem | Excel Worksheet Functions | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Formula Problem - If Statement | Excel Worksheet Functions | |||
FORMULA REQD FOR ADD ING DATES AND VALUES AND PART NUMBERS | Excel Discussion (Misc queries) | |||
If Statement Problem Part 2! | Excel Programming |