# Thread: Can IF statements be done with dates???

1. Registered User
Join Date
Mar 2005
Posts
25

## Unanswered: Can IF statements be done with dates???

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).

2. Registered User
Join Date
Oct 2003
Posts
1,091
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.

3. Registered User
Join Date
Mar 2005
Posts
25
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
Last edited by mike703; 03-04-05 at 13:18.

4. Registered User
Join Date
Oct 2003
Posts
1,091
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.
Last edited by shades; 03-04-05 at 13:53.

5. Registered User
Join Date
Mar 2004
Location
Minnesota, USA, Earth
Posts
65

## Suggested Solution

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
Last edited by SR22Mike; 03-04-05 at 14:23.

6. Registered User
Join Date
Mar 2005
Posts
25
Got if working perfect....thank you guys very much!!! will probably be back very soon!!!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•