+ Reply to Thread
Results 1 to 5 of 5

Date formula :/

  1. #1
    Registered User
    Join Date
    01-27-2011
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Question Date formula :/

    I have luckily been able to put together most of the help I need from other threads and postings, but I am still a little short of my answer. Should be an easy one for an eperienced person though.

    Problem: I have =Today() in A2 and I have an expiration date in C4. Now my formula cell is G2 and it has =IF((C4-A2)<30,1,IF((C4-A2)<14,2,0)).
    The issue with this formula is that when there are less than 30 days before expiration it shows a 1 even if it is under 14 days away. I need it to show that 2 if it's under 14 but can't get it. Only a 1 or 0. Thanks for anyone who can help!

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Date formula :/

    Change the conditions' order =IF((C4-A2)<14,2,IF((C4-A2)<30,1,0)).

  3. #3
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Date formula :/

    I'll give you an explanation for the IF() function and I'm sure you'll figure it out

    The function looks at each condition in order. As soon as it finds a condition that is true it stops and provides the result you want for that condition.

    Your first condition is true before your second condition gets looked at.

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Date formula :/

    this formula would also give you the required result

    =(C4-A2<30)+(C4-A2<14)
    Audere est facere

  5. #5
    Registered User
    Join Date
    01-27-2011
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Date formula :/

    That got it, I used daddylonglegs's formula cuz I'm a sucker for simplicity but now I understand the function thanks to you others. I really appreciate it!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1