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

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

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. "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. [QUOTE=caabyyc;19238]"date"=A1, "another list"=F:F

LOOKUP(A1,F:F)

Originally Posted by LG2008
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. 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.

5. I have replied in your other thread with the same topic.... please try my formula again.

6. Originally Posted by NBVC