+ Reply to Thread
Results 1 to 8 of 8

If statement with Dates

  1. 07-02-2013, 03:06 PM #1
    ktkreeger
    ktkreeger is offline
    Registered User
    Join Date
    07-02-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    22

    If statement with Dates

    Hello and Thank you for the assistance.

    I've read a lot of threads and tried so many things before getting to this point of posting a question. I appreciate any help.


    Question:
    I have one date that can be in three different columns. Two of the columns could be blank. In A1 I want to find the difference between one of those dates compared to (Today) which will result in a value in days.

    Example
    A1 = formula

    B1 = Blank
    C1 = Blank
    D1 = July 6, 2013

    Formula should check if there is a value in B1, if blank go to the next cell C1, if blank then go to D1. Then it will calculate the difference between D1 date and today's date, and return a value of '4'.

    Thanks for the help,
    Ktkreeger
    Reply With QuoteRegister To Reply
  2. 07-02-2013, 03:15 PM #2
    BB1972
    BB1972 is offline
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: If statement with Dates

    ktkreeger, welcome to the forum.

    In A1, the following formula will work as you require:

    Please Login or Register  to view this content.
    Make sure your DATE entry is as a date, ie: 06/07/2013 (or 07/06/2013 in your neck of the woods.)

    Hope this helps.
    Brendan.


    __________________________________________________________________________________________________
    Things to consider:

    1) You can thank any poster by clicking the * at the left of a helpful post.
    2) You can help to keep the forum tidy by marking your thread as "Solved", if it has been answered to your satisfaction.
    3) Help us to help you, by uploading a sample workbook, showing the type of data you're dealing with, and clearly indicating what the results should be.
    Reply With QuoteRegister To Reply
  3. 07-02-2013, 03:25 PM #3
    ktkreeger
    ktkreeger is offline
    Registered User
    Join Date
    07-02-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: If statement with Dates

    Solved. Thanks Brendan!

    New to forum so I made sure to give you a star for the reputation. Trying to do step two to marked solve and I'm not sure how to change the title.

    Thanks again for the quick help.
    Reply With QuoteRegister To Reply
  4. 07-02-2013, 03:26 PM #4
    Teethless mama
    Teethless mama is offline
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: If statement with Dates

    =abs(lookup(1e99,b:b)-today())
    Reply With QuoteRegister To Reply
  5. 07-02-2013, 03:28 PM #5
    BB1972
    BB1972 is offline
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: If statement with Dates

    Glad to help, ktkreeger, and thanks for the rep.

    To mark your thread "Solved", above your initial post and to the right, there should be a "Thread Tools" dropdown, which contains "Mark solved". I'm sure you'll figure it out from there!
    Reply With QuoteRegister To Reply
  6. 07-02-2013, 03:34 PM #6
    BB1972
    BB1972 is offline
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: If statement with Dates

    @ktkreeger, Teethless mama's formula is actually a far more manageable one than the one I provided, as it (appears to) look up the largest/highest value in Column B, and subtracts today's date from it.

    @Teethless, am I correct in saying that looking up "1E+99" is looking for the largest value in B:B?
    Reply With QuoteRegister To Reply
  7. 07-02-2013, 04:14 PM #7
    Teethless mama
    Teethless mama is offline
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: If statement with Dates

    Quote Originally Posted by BB1972 View Post
    @ktkreeger, Teethless mama's formula is actually a far more manageable one than the one I provided, as it (appears to) look up the largest/highest value in Column B, and subtracts today's date from it.

    @Teethless, am I correct in saying that looking up "1E+99" is looking for the largest value in B:B?
    Lookup 1E99 is very large number much larger than any "date values" so the formula return the last value in a range.
    Last edited by Teethless mama; 07-02-2013 at 04:18 PM.
    Reply With QuoteRegister To Reply
  8. 07-02-2013, 04:26 PM #8
    BB1972
    BB1972 is offline
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: If statement with Dates

    Good to know - thanks for clarifying, Teethless mama.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1