Results 1 to 6 of 6

Thread: Get Date prior to Actual Date

  1. #1
    Junior Member
    Join Date
    Jul 2013
    Posts
    18
    Rep Power
    0

    Get Date prior to Actual Date

    Hi

    I would like to re create my previous post,
    I thought I can sort things out by just getting the formula for the Date prior to actual date, but now I really need help.
    Ok, so here is my situation,
    I need to get the Date Prior to Actual Date but it has multiple criteria.
    I need a formula that is flexible so that I will just drag it down and it will calculate correctly.
    I will attach the sample file I have so far, I also used the formula given by "Bebo"

    sample.xlsx

    I hope you guys out there could help me out. Thank you so much.

    I also posted this question in below link: crosspost:
    http://www.mrexcel.com/forum/excel-q...tual-date.html
    http://www.excelforum.com/excel-form...tual-date.html
    http://www.vbaexpress.com/forum/show...to-Actual-Date
    http://www.excelfox.com/forum/f2/get...ual-date-1992/
    Attached Images Attached Images
    Last edited by mackypogi; 02-26-2015 at 08:18 AM.

  2. #2

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Here's how you can get date prior to a date

    =INDEX($B$4:$B$19,MATCH(B5-1/9^9,$B$4:$B$19,1))
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    And the solution to your Excel file

    Use K5=F5+INDEX($H$4:$H$19,MATCH(B5-1/10^9,$B$4:$B$19,1))+INDEX($J$4:$J$19,MATCH(B5-1/10^9,$B$4:$B$19,1)) and drag down
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Junior Member
    Join Date
    Jul 2013
    Posts
    18
    Rep Power
    0
    Hi Excel Fox,

    Thank you for your response, I will try and get back to you

  6. #6
    Junior Member
    Join Date
    Jul 2013
    Posts
    18
    Rep Power
    0
    Quote Originally Posted by mackypogi View Post
    Hi

    I would like to re create my previous post,
    I thought I can sort things out by just getting the formula for the Date prior to actual date, but now I really need help.
    Ok, so here is my situation,
    I need to get the Date Prior to Actual Date but it has multiple criteria.
    I need a formula that is flexible so that I will just drag it down and it will calculate correctly.
    I will attach the sample file I have so far, I also used the formula given by "Bebo"

    sample.xlsx

    I hope you guys out there could help me out. Thank you so much.

    I also posted this question in below link: crosspost:
    Get Date prior to Actual Date
    Get Date prior to actual Date
    Date Prior to Actual Date
    http://www.excelfox.com/forum/f2/get...ual-date-1992/
    Guys, I updated my post, and hopefully you can help me out.

Similar Threads

  1. Replies: 4
    Last Post: 02-03-2014, 07:01 PM
  2. Replies: 1
    Last Post: 11-20-2013, 09:14 AM
  3. Compare Date Columns In Excel And Pull The Latest Date
    By marentette010 in forum Excel Help
    Replies: 1
    Last Post: 07-31-2013, 11:56 PM
  4. Replies: 5
    Last Post: 06-15-2013, 12:40 PM
  5. Replace Incorrect Date In Cell To Another Valid Date
    By DARSHANKmandya in forum Excel and VBA Tips and Tricks
    Replies: 2
    Last Post: 03-21-2013, 09:27 PM

Tags for this Thread

Posting Permissions

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