was wondering if somebody out there would be able to help me. Currently working with a spreadsheet that has date values (ex. 3/2/02), wanted to find out how to use an IF function to get excel to recognize a date as being in a specific quarter....for example

10/1-12/31 = Q1
1/1-3/31 = Q2
4/1-6/30 = Q3
7/1-9/30 = Q4

The formula i came up with is below:

=IF(D105>9/30,"Q1", IF(D105>12/31,"Q2",IF(D105>3/31, "Q3", IF(D105>6/30, "Q4"))))

D105 is the date (3/15/2002)....using the formula, the end result is "Q1" which is incorrect. (The result for all dates is "Q1" using the current formula).

What am I doing wrong?? Please help!!

You should specify the year as well in the date.

Another approach is to set up a lookup table and use that as a reference.

I tried using the year...but makes no difference (would be irrelivant anyways because I am working with more than one year or data). I tried using the LOOKUP funtion as well but only got errors in the formula.

Example

=LOOKUP(M106,{10/1,1/1,4/1,7/1},{"Q1","Q2","Q3","Q4"})

this didn't even work...only got errors!

PS - M106 = 3/15/2002

I put Start in A1 and End in B1 and Qtr in C1

Then put the dates (with month/day) in A2:B5 and corresponding Qtr in C2:C5.

Then I put the trigger date in E2. Then use this formula:

=IF(AND(E2>=A2,E2<=B2),C2,IF(AND(E2>=A3,E2<=B3),C3 ,IF(AND(E2>=A4,E2<=B4),C4,C5)))

Be sure that all your dates are formatted the same way.

Try this formula:

=IF(MONTH(A1)>6,IF(MONTH(A1)>9,"Q1","Q4"),IF(MONTH (A1)>3,"Q3","Q2"))

I don't know if it is the most elegant solution, but it should work.

This is setup for if Quarter 1 is 10/1 to 12/31.

If you want standard quarters use this:

=IF(MONTH(A1)>6,IF(MONTH(A1)>9,"Q4","Q3"),IF(MONTH (A1)>3,"Q2","Q1"))

Enjoy!
Mike

Got if working perfect....thank you guys very much!!! will probably be back very soon!!!