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

L

LG2008

New member
Joined
Jul 2, 2015
Messages
6
Reaction score
0
Points
0
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
 
caabyyc said:
"date"=A1, "another list"=F:F


LOOKUP(A1,F:F)




LG2008 said:
Hi all, any idea how I would round a date up to the nearest date in another list?
Click to expand...

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.
Click to expand...
 
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:
I have replied in your other thread with the same topic.... please try my formula again.
 
NBVC said:
I have replied in your other thread with the same topic.... please try my formula again.
Click to expand...

Thanks - I hadn't realized (from this thread) that the dates needed to be calculated rather than pre-listed.
 
You must log in or register to reply here.
Back
Top