Results 1 to 6 of 6
  1. #1
    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).

    What am I doing wrong?? Please help!!

  2. #2
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Mar 2005
    Posts
    25

    Unhappy

    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 14:18.

  4. #4
    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 14:53.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    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 15:23.

  6. #6
    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
  •