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

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

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

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

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

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

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

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