Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
gls858
 
Posts: n/a
Default 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   Report Post  
FxM
 
Posts: n/a
Default

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   Report Post  
PeterAtherton
 
Posts: n/a
Default

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   Report Post  
CLR
 
Posts: n/a
Default

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   Report Post  
gls858
 
Posts: n/a
Default

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   Report Post  
gls858
 
Posts: n/a
Default

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   Report Post  
gls858
 
Posts: n/a
Default

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   Report Post  
gls858
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
formula will not fill correctly due to odd number rows Barb Excel Worksheet Functions 3 May 10th 05 09:10 PM
Fill formula question Meredith Excel Worksheet Functions 1 March 29th 05 11:19 AM
Possible? formula in one cell can fill another cell Sherri Excel Worksheet Functions 3 March 24th 05 01:35 AM
Formula to count the cells in a range that have a fill color. Slainteva Excel Discussion (Misc queries) 2 January 19th 05 09:25 PM
Formula to count the cells in a range that have a fill color. Molly F Excel Discussion (Misc queries) 2 January 19th 05 07:15 PM


All times are GMT +1. The time now is 12:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"