Formula to fill inthe Quarter

  • Thread starter Thread starter gls858
  • Start date Start date
G

gls858

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
Click to expand...


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
 
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
 
CLR said:
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
Click to expand...
Thank you all for the suggestions. I'll give them a try tomorrow.

gls858
 
FxM said:
gls858 a écrit :




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
Click to expand...
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 said:
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

:
Click to expand...
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 said:
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
Click to expand...
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
 
Back
Top