Finding the nearest day

**Salomey** · 12-02-2010, 06:30 AM

Hello guys!

Sometimes I need to calculate different coefficients and ratios for the specified period (only weekdays).
To do this, I usually enter manually starting and ending date in the cells. But due to the fact that I have only weekdays I periodically get an error #N/A, because there is no such date in my sample (for example it was a day off and i skipped it).

I wonder, if there is a way in Excel to give a hint what is the nearest day to that I entered manually so I don't have to scroll down my sample to find it visually.

Please, take a look at the attached file.

Thanks in advance.

**Richard Buttrey** · 12-02-2010, 07:14 AM

Hi,

Just leave out the ,0 bit of the Match() function. This will return the day before the day selected. if you want the day after just add a +1. Of course since you'll now always find a Match there's no need for an IF(ISERROR()) test.

i.e.

Please Login or Register  to view this content.

or leave out the +1 for the day before.

HTH

**TMS** · 12-02-2010, 07:22 AM

Try changing the match option from 01 to 1

=IF(ISERROR(MATCH($J$7,$C:$C,1)),"Jan22 is a Day Off, ...???",INDEX($E:$E,MATCH(J3,$C:$C,1)))

21/01/2005
9889.46 21/01/2005

22/01/2005
9889.46 21/01/2005

23/01/2005
9889.46 21/01/2005

24/01/2005
9946 24/01/2005

25/01/2005
9996.66 25/01/2005

**Salomey** · 12-02-2010, 07:48 AM

Thank you for your replies guys.
But that is a little bit not what I want.
The main variable in my case is date. I'd like Excel to automatically change the date to the nearest or if it is impossible to get a message in cell J8 (in my example) something like "DayOff, The nearest date is...".

Is it possible?
Anyway, thanks again.

**Richard Buttrey** · 12-02-2010, 09:15 AM

Hi,

In that case in J8

Please Login or Register  to view this content.

Rgds

**Salomey** · 12-02-2010, 10:43 AM

Thank you for your reply. Great idea, but your code and some modifications of it always return empty cell ("").
I tried only that portion of code

Please Login or Register  to view this content.

and I see that nothing happens when I enter the date which is not in my sample, for example 22.01.05.

Do you have any ideas on this?
Thanks

**Richard Buttrey** · 12-02-2010, 01:24 PM

Hi,

First you're missing a close parentheses after the '1', and the date needs to be entered with a '/' separator not a '.'

Correct those and you'll get the result 21/01/05

Rgds