+ Reply to Thread
Results 1 to 12 of 12

Find the date closer to another with a condition

  1. 05-12-2015, 11:26 AM #1
    tsakta13ole
    tsakta13ole is offline
    Forum Contributor
    Join Date
    10-26-2013
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2013
    Posts
    248

    Find the date closer to another with a condition

    RSX DATA19.xlsm

    Hi. I've attached my spreadsheet. In column F I want to compare column P and column B and come up with the difference in days under a condition. I will give an example because english is not my native language so you probably did not understand what I'm trying to do.
    In cell F33 I want to compare P4 with B33 and if P4 is over 16 days apart from B33 then F33 to be (P4-B33)/365. I have already done that with an IF statement. But this IF statement doesn't work in later rows. Btw I only care about odd cells, F33,F35 so I have hiden even cells. Does it need a lookup?

    Hope you understand what I'm trying to do!
    Reply With QuoteRegister To Reply
  2. 05-12-2015, 11:32 AM #2
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,131

    Re: Find the date closer to another with a condition

    do you want to compare P4 sith F35 , F37 etc
    if so you need a $ to stop the row changing
    (P$4-B33)/365
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  3. 05-12-2015, 11:35 AM #3
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.92 (24120731))
    Posts
    9,131

    Re: Find the date closer to another with a condition

    do you want to compare P4 sith F35 , F37 etc
    if so you need a $ to stop the row changing
    (P$4-B33)/365
    Reply With QuoteRegister To Reply
  4. 05-12-2015, 11:38 AM #4
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Find the date closer to another with a condition

    Hello. Καλησπέρα!!

    Your formula in F33 is this one.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    As P4-b33 ISNOT bigger than 16(actually is 15), your formula gives correctly as result the number 14, that is the result of the second condtion of your formula((P5-B33)/365)).

    What is the expected result in F33 and why?
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  5. 05-12-2015, 11:52 AM #5
    tsakta13ole
    tsakta13ole is offline
    Forum Contributor
    Join Date
    10-26-2013
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2013
    Posts
    248

    Re: Find the date closer to another with a condition

    Thank you both for you answers! Καλησπερα to you!

    I have some stock prices and I want to calculate the price of the options. To do that I need the time to expiration of each option in years. So in F33 what I want is (21/02/2014 - 02/01/2014)/365. For F33 the formula is correct. For F35 17/01/2014 is less than -let's say- 55 days from 21/02/2014. So the result in 35 need to be (21/02/2014-17/01/2014). For F37 the result needs to be (21/02/2014-03/02/2014)/365. For F39 (21/03/2014-21/02/2014)/365.
    I can do this manually but I would prefer to find a formula.
    Thank you for any help!
    Reply With QuoteRegister To Reply
  6. 05-12-2015, 12:12 PM #6
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Find the date closer to another with a condition

    Unfortunately, i am not able to understand the logic. In your first post(also in your formula) you are talking for 16 days(IF((P6-B35)>16.....). In your reply you are talking for 55 days(..... For F35 17/01/2014 is less than -let's say- 55 days ...) So iam comfusing.

    Perhaps it's just me and some one else to get better your point.

    In any case if you don't find a solution from some other member, then pm your explanations in Greek and i'll see it tomorrow morning, as it's time to leave the office now!

    Good luck.
    Reply With QuoteRegister To Reply
  7. 05-12-2015, 12:18 PM #7
    tsakta13ole
    tsakta13ole is offline
    Forum Contributor
    Join Date
    10-26-2013
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2013
    Posts
    248

    Re: Find the date closer to another with a condition

    More than 16 and less than 55 days. I will pm you if noone else replies. Thanks
    Reply With QuoteRegister To Reply
  8. 05-12-2015, 12:23 PM #8
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Find the date closer to another with a condition

    Like this?

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 05-12-2015, 12:58 PM #9
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,659

    Re: Find the date closer to another with a condition

    Look like the date in B33 will be added up 16 days, then find the closest date in P column that is greater than?
    For example:
    B33 +16 = 1/2/2014 +16 = 1/18/2014 ==> result is P5 = 2/21/2014
    B35 +16 = 1/17/2014 +16 = 2/2/2014 ==> result is P5 = 2/21/2014
    B39 +16 = 1/17/2014 +16 = 3/9/2014 ==> result is P6 = 3/21/2014
    Is that the logic?
    If yes, try:
    Please Login or Register  to view this content.
    Quang PT
    Reply With QuoteRegister To Reply
  10. 05-12-2015, 12:59 PM #10
    tsakta13ole
    tsakta13ole is offline
    Forum Contributor
    Join Date
    10-26-2013
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2013
    Posts
    248

    Re: Find the date closer to another with a condition

    This works! Thanks!
    Last edited by tsakta13ole; 05-12-2015 at 01:12 PM.
    Reply With QuoteRegister To Reply
  11. 05-12-2015, 01:20 PM #11
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,659

    Re: Find the date closer to another with a condition

    Nice to hear it works.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  12. 05-22-2015, 08:29 AM #12
    tsakta13ole
    tsakta13ole is offline
    Forum Contributor
    Join Date
    10-26-2013
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2013
    Posts
    248

    Re: Find the date closer to another with a condition

    I have a problem with this formula =(LOOKUP(B33+16,$P$4:$P$18,$P$5:$P$19)-B33)/365 offered above and I am the thread starter. Should I click unsolved for the thread and ask my question here or start a new thread?

    edit: Guess I will start a new thread.
    Last edited by tsakta13ole; 05-22-2015 at 10:24 AM.
    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)

Similar Threads

  1. [SOLVED]Find a closer date for payroll in a range of date
    By jackson_hollon in forum Excel General
    Replies: 9
    Last Post: 10-06-2014, 11:06 AM
  2. [SOLVED] How do you make cells change color as the indicated date come closer?
    By MIZUGAMI in forum Excel General
    Replies: 5
    Last Post: 09-19-2012, 02:32 PM
  3. Automatic mail on due date (30 days or closer)
    By medioman in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-16-2012, 05:32 AM
  4. [SOLVED] Find Last Entry (Date) in a List satisfying a Condition
    By rau in forum Excel General
    Replies: 5
    Last Post: 09-03-2012, 02:10 AM
  5. Find closer higher value
    By caquico in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-08-2010, 08:51 PM

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