Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column value for row = fiscal quarter (determined by date field)
Hi All,
I have a sheet (roughly 6k rows) in which I've inserted a column to assign a fiscal quarter label dependent on the date in the column following it. Eg: A B C etc.... Fiscal Quarter Date Data (dependent on B) 8/7/07 Case data and other irrelevants... I attempted doing a multi-nested IF statement to assign "Q1 07", "Q2 07", "Q3 07" or "Q4 07" depending on the date range and no matter what the actual related date is, it invariably results in "Q1 07". =IF(12/2007<A34/2007, "Q1 07", IF(3/2007<A37/2007, "Q2 07", IF(6/2007<A310/2007, "Q3 07", IF(9/2007<A31/2008, "Q4 07")))) (I also tried this using the DATEVALUE format for the dates, just in case Excel wasn't reading my ranges as dates. No go with that either.) I'm sure there's an easy way to do this, I'm just not finding it. Thanks, Jamie |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column value for row = fiscal quarter (determined by date field)
Try this in A2:
="Q"&INT((MONTH(B2)-1)/3)+1&" "&TEXT(B2,"yy") Hope this helps. Pete On Aug 3, 4:16 pm, MJW wrote: Hi All, I have a sheet (roughly 6k rows) in which I've inserted a column to assign a fiscal quarter label dependent on the date in the column following it. Eg: A B C etc.... Fiscal Quarter Date Data (dependent on B) 8/7/07 Case data and other irrelevants... I attempted doing a multi-nested IF statement to assign "Q1 07", "Q2 07", "Q3 07" or "Q4 07" depending on the date range and no matter what the actual related date is, it invariably results in "Q1 07". =IF(12/2007<A34/2007, "Q1 07", IF(3/2007<A37/2007, "Q2 07", IF(6/2007<A310/2007, "Q3 07", IF(9/2007<A31/2008, "Q4 07")))) (I also tried this using the DATEVALUE format for the dates, just in case Excel wasn't reading my ranges as dates. No go with that either.) I'm sure there's an easy way to do this, I'm just not finding it. Thanks, Jamie |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column value for row = fiscal quarter (determined by date field)
On Aug 3, 8:16 am, MJW wrote:
I have [...] A B C etc.... Fiscal Quarter Date Data (dependent on B) 8/7/07 Case data and other irrelevants... I attempted doing a multi-nested IF statement to assign "Q1 07", "Q2 07", "Q3 07" or "Q4 07" depending on the date range and no matter what the actual related date is, it invariably results in "Q1 07". =IF(12/2007<A34/2007, "Q1 07", IF(3/2007<A37/2007, "Q2 07", IF(6/2007<A310/2007, "Q3 07", IF(9/2007<A31/2008, "Q4 07")))) You cannot express a range condition (x < y < z) that way -- or even the way I did it parenthetically. You need to use the AND() function. For example: and(1<=month(A3), month(A3)<=3). However, there is a simpler way to formulate the IF() expression so that you avoid complex comparisons. For example: =if(month(A3)=10, "Q4 07", if(month(A3)=7, "Q3 07", if(month(A3)=4, "Q2 07", "Q1 07"))) Alternatively: ="Q" & 1+int((month(A3)-1)/3) & " 07" And more generally: ="Q" & 1+int((month(A3)-1)/3) & " " & text(year(A3),"yy") |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column value for row = fiscal quarter (determined by date fiel
Thanks Pete, that's exactly what I was looking for--worked like a charm!
Much appreciated, Jamie "Pete_UK" wrote: Try this in A2: ="Q"&INT((MONTH(B2)-1)/3)+1&" "&TEXT(B2,"yy") Hope this helps. Pete On Aug 3, 4:16 pm, MJW wrote: Hi All, I have a sheet (roughly 6k rows) in which I've inserted a column to assign a fiscal quarter label dependent on the date in the column following it. Eg: A B C etc.... Fiscal Quarter Date Data (dependent on B) 8/7/07 Case data and other irrelevants... I attempted doing a multi-nested IF statement to assign "Q1 07", "Q2 07", "Q3 07" or "Q4 07" depending on the date range and no matter what the actual related date is, it invariably results in "Q1 07". =IF(12/2007<A34/2007, "Q1 07", IF(3/2007<A37/2007, "Q2 07", IF(6/2007<A310/2007, "Q3 07", IF(9/2007<A31/2008, "Q4 07")))) (I also tried this using the DATEVALUE format for the dates, just in case Excel wasn't reading my ranges as dates. No go with that either.) I'm sure there's an easy way to do this, I'm just not finding it. Thanks, Jamie |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column value for row = fiscal quarter (determined by date fiel
Thanks for feeding back, Jamie - glad it worked for you.
Pete On Aug 3, 7:00 pm, MJW wrote: Thanks Pete, that's exactly what I was looking for--worked like a charm! Much appreciated, Jamie "Pete_UK" wrote: Try this in A2: ="Q"&INT((MONTH(B2)-1)/3)+1&" "&TEXT(B2,"yy") Hope this helps. Pete On Aug 3, 4:16 pm, MJW wrote: Hi All, I have a sheet (roughly 6k rows) in which I've inserted a column to assign a fiscal quarter label dependent on the date in the column following it. Eg: A B C etc.... Fiscal Quarter Date Data (dependent on B) 8/7/07 Case data and other irrelevants... I attempted doing a multi-nested IF statement to assign "Q1 07", "Q2 07", "Q3 07" or "Q4 07" depending on the date range and no matter what the actual related date is, it invariably results in "Q1 07". =IF(12/2007<A34/2007, "Q1 07", IF(3/2007<A37/2007, "Q2 07", IF(6/2007<A310/2007, "Q3 07", IF(9/2007<A31/2008, "Q4 07")))) (I also tried this using the DATEVALUE format for the dates, just in case Excel wasn't reading my ranges as dates. No go with that either.) I'm sure there's an easy way to do this, I'm just not finding it. Thanks, Jamie- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert a specific date to a fiscal quarter ? | Excel Discussion (Misc queries) | |||
Fiscal Quarter Conversion | Excel Worksheet Functions | |||
fiscal quarter conversion | Excel Discussion (Misc queries) | |||
Fiscal Year in date field | Excel Discussion (Misc queries) | |||
Date formula: return Quarter and Fiscal Year of a date | Excel Discussion (Misc queries) |