Results 1 to 6 of 6

Thread: Rounding Date Up to Nearest Date in List (without array...?)

  1. #1

    Rounding Date Up to Nearest Date in List (without array...?)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all, any idea how I would round a date up to the nearest date in another list? - To save memory in the file I suppose it would be preferable if this wasn't an array, but open to all suggestions that solves the problem!

    Thanks

  2. #2
    Acolyte caabyyc's Avatar
    Join Date
    Dec 2014
    Location
    Burnaby/Calgary
    Posts
    26
    Articles
    0
    Excel Version
    2016
    "date"=A1, "another list"=F:F


    LOOKUP(A1,F:F)




    [QUOTE=LG2008;19235]Hi all, any idea how I would round a date up to the nearest date in another list?

  3. #3
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    [QUOTE=caabyyc;19238]"date"=A1, "another list"=F:F


    LOOKUP(A1,F:F)




    Quote Originally Posted by LG2008 View Post
    Hi all, any idea how I would round a date up to the nearest date in another list?
    The problem with lookup is that it picks the match that is less than the search value, rather than rounding up as required here. I'm not sure if this can be got around by (e.g.) changing the sequence of the data array.

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Try this Array Formula:

    {=INDEX(D : D,MATCH(MIN(ABS($B$1-D : D)),ABS($B$1-D : D),0))}

    This assumes that your table of dates to select from is in Col D, and the date you're looking for is in B1. Remember to press Ctrl+Shift+Enter to get Excel to add the curly brackets needed for an array formula.
    Where I've typed D : D above to stop the editor putting a smiley in do it in the formula without the spaces.
    Last edited by Hercules1946; 2015-07-10 at 08:29 PM.

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    I have replied in your other thread with the same topic.... please try my formula again.


  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by NBVC View Post
    I have replied in your other thread with the same topic.... please try my formula again.
    Thanks - I hadn't realized (from this thread) that the dates needed to be calculated rather than pre-listed.

Posting Permissions

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