Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula to fill inthe Quarter
I seem to always have trouble when dealing with dates in Excel.
Is it possible to have a formula the would look at the date field and fill in the quarter in col 1? Dates 01/01/02 - 03/31/02 = Qtr1 and so forth. Sample data: A B C Qtr Invoice Date 325938-0 1/2/2002 326529-0 1/4/2002 326910-1 1/7/2002 326942-0 1/7/2002 C 324534-0 1/9/2002 328088-0 1/10/2002 gls858 |
#2
|
|||
|
|||
gls858 a écrit :
I seem to always have trouble when dealing with dates in Excel. Is it possible to have a formula the would look at the date field and fill in the quarter in col 1? Dates 01/01/02 - 03/31/02 = Qtr1 and so forth. Sample data: A B C Qtr Invoice Date 325938-0 1/2/2002 326529-0 1/4/2002 326910-1 1/7/2002 326942-0 1/7/2002 C 324534-0 1/9/2002 328088-0 1/10/2002 gls858 Hi gls858, In that case, consider the month and not the date. Quarter has a relation with 3 months. Why not =int((month(C1)+2)/3) ? HTH FxM |
#3
|
|||
|
|||
Try this and copy down
=IF(MONTH(A1)<4,"Qtr 1",IF(MONTH(A1)<7,"Qtr 2",IF(MONTH(A1)<10,"Qtr 3","Qtr 4"))) regards Peter "gls858" wrote: I seem to always have trouble when dealing with dates in Excel. Is it possible to have a formula the would look at the date field and fill in the quarter in col 1? Dates 01/01/02 - 03/31/02 = Qtr1 and so forth. Sample data: A B C Qtr Invoice Date 325938-0 1/2/2002 326529-0 1/4/2002 326910-1 1/7/2002 326942-0 1/7/2002 C 324534-0 1/9/2002 328088-0 1/10/2002 gls858 |
#4
|
|||
|
|||
Another way..............
Down column I, put 1/1/2002 3/31/2002 6/30/2002 9/30/2002 Down column J, put 1st 2nd 3rd 4th In A2 put this formula and copy down.......... =VLOOKUP(C2,$I$1:$J$4,2,TRUE) Vaya con Dios, Chuck, CABGx3 "gls858" wrote in message ... I seem to always have trouble when dealing with dates in Excel. Is it possible to have a formula the would look at the date field and fill in the quarter in col 1? Dates 01/01/02 - 03/31/02 = Qtr1 and so forth. Sample data: A B C Qtr Invoice Date 325938-0 1/2/2002 326529-0 1/4/2002 326910-1 1/7/2002 326942-0 1/7/2002 C 324534-0 1/9/2002 328088-0 1/10/2002 gls858 |
#5
|
|||
|
|||
CLR wrote:
Another way.............. Down column I, put 1/1/2002 3/31/2002 6/30/2002 9/30/2002 Down column J, put 1st 2nd 3rd 4th In A2 put this formula and copy down.......... =VLOOKUP(C2,$I$1:$J$4,2,TRUE) Vaya con Dios, Chuck, CABGx3 "gls858" wrote in message ... I seem to always have trouble when dealing with dates in Excel. Is it possible to have a formula the would look at the date field and fill in the quarter in col 1? Dates 01/01/02 - 03/31/02 = Qtr1 and so forth. Sample data: A B C Qtr Invoice Date 325938-0 1/2/2002 326529-0 1/4/2002 326910-1 1/7/2002 326942-0 1/7/2002 C 324534-0 1/9/2002 328088-0 1/10/2002 gls858 Thank you all for the suggestions. I'll give them a try tomorrow. gls858 |
#6
|
|||
|
|||
FxM wrote:
gls858 a écrit : I seem to always have trouble when dealing with dates in Excel. Is it possible to have a formula the would look at the date field and fill in the quarter in col 1? Dates 01/01/02 - 03/31/02 = Qtr1 and so forth. Sample data: A B C Qtr Invoice Date 325938-0 1/2/2002 326529-0 1/4/2002 326910-1 1/7/2002 326942-0 1/7/2002 C 324534-0 1/9/2002 328088-0 1/10/2002 gls858 Hi gls858, In that case, consider the month and not the date. Quarter has a relation with 3 months. Why not =int((month(C1)+2)/3) ? HTH FxM Thanks for the help. Interesting method. Your formula worked just fine. I just needed to change C1 to C2 since it was the first cell with a date. C1 contained the col name. gls858 |
#7
|
|||
|
|||
PeterAtherton wrote:
Try this and copy down =IF(MONTH(A1)<4,"Qtr 1",IF(MONTH(A1)<7,"Qtr 2",IF(MONTH(A1)<10,"Qtr 3","Qtr 4"))) regards Peter "gls858" wrote: I seem to always have trouble when dealing with dates in Excel. Is it possible to have a formula the would look at the date field and fill in the quarter in col 1? Dates 01/01/02 - 03/31/02 = Qtr1 and so forth. Sample data: A B C Qtr Invoice Date 325938-0 1/2/2002 326529-0 1/4/2002 326910-1 1/7/2002 326942-0 1/7/2002 C 324534-0 1/9/2002 328088-0 1/10/2002 gls858 Thanks for the help. This was the type of formula I was attempting but was unable to make mine work. Your formula worked after a slight adjustment. I changed the A1 to C2. gls858 |
#8
|
|||
|
|||
CLR wrote:
Another way.............. Down column I, put 1/1/2002 3/31/2002 6/30/2002 9/30/2002 Down column J, put 1st 2nd 3rd 4th In A2 put this formula and copy down.......... =VLOOKUP(C2,$I$1:$J$4,2,TRUE) Vaya con Dios, Chuck, CABGx3 "gls858" wrote in message ... I seem to always have trouble when dealing with dates in Excel. Is it possible to have a formula the would look at the date field and fill in the quarter in col 1? Dates 01/01/02 - 03/31/02 = Qtr1 and so forth. Sample data: A B C Qtr Invoice Date 325938-0 1/2/2002 326529-0 1/4/2002 326910-1 1/7/2002 326942-0 1/7/2002 C 324534-0 1/9/2002 328088-0 1/10/2002 gls858 Another interesting solution. Worked first time. Thanks for the help. Just goes to show there are many ways to skin the same cat, so to speak :-) gls858 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula will not fill correctly due to odd number rows | Excel Worksheet Functions | |||
Fill formula question | Excel Worksheet Functions | |||
Possible? formula in one cell can fill another cell | Excel Worksheet Functions | |||
Formula to count the cells in a range that have a fill color. | Excel Discussion (Misc queries) | |||
Formula to count the cells in a range that have a fill color. | Excel Discussion (Misc queries) |