Find closest date to today

[solarissf]

Hello All,

I have a column that is NOT sorted and cannot be (with dates in it). I want to search the column, and highlight the cell that is closest to today.

I found this post, but does not work for me.

http://www.mrexcel.com/forum/showthread.php?t=326266

I believe it is because, if I try and convert the dates to NUMBERS, it will not let me. Possibly the source of the data is doing something to the format that will not let me switch the format.

Any ideas for finding the closest date?

thanks

 

[T. Valko]

Hello All,

I have a column that is NOT sorted and cannot be (with dates in it). I want to search the column, and highlight the cell that is closest to today.

I found this post, but does not work for me.

http://www.mrexcel.com/forum/showthread.php?t=326266

I believe it is because, if I try and convert the dates to NUMBERS, it will not let me. Possibly the source of the data is doing something to the format that will not let me switch the format.

Any ideas for finding the closest date?

thanks

Which date is closest to today (5/23/2011) and should be highlighted:

5/22/2011
5/24/2011

 

[solarissf]

I should have mentioned... the dates will always be sometime in the future... they will never be in the past.

 

[Aladin Akyurek]

Hello All,

I have a column that is NOT sorted and cannot be (with dates in it). I want to search the column, and highlight the cell that is closest to today.

I found this post, but does not work for me.

http://www.mrexcel.com/forum/showthread.php?t=326266

I believe it is because, if I try and convert the dates to NUMBERS, it will not let me. Possibly the source of the data is doing something to the format that will not let me switch the format.

Any ideas for finding the closest date?

thanks

Control+shift+enter, not just enter:

=INDEX(A2:A15,MATCH(MIN(ABS(A2:A15-TODAY())),ABS(A2:A15-TODAY()),0))

where A2:A15 houses a sample of (unsorted) dates. Format the formula cell
as date. Note that a date is simply an integer (number) in Excel.

 

[solarissf]

that is the problem... there is something screwy with the source format. It will not let me format date to a number. Which leads me to believe maybe its some sort of text or custom.

 

[T. Valko]

I should have mentioned... the dates will always be sometime in the future... they will never be in the past.

If the dates will always be in the future then all you need is the minimum date. The earliest date will be the closest to today's date.

=MIN(A1:A10)

Format as Date

 

[solarissf]

here seems to be the problem, when I copy and paste the date to a new sheet

Range(Cells(startRow, 1), Cells(endRow, 12)).Activate
Selection.Copy
Workbooks("FirstNoticeEmail.xlsx").Worksheets(1).Range("A7").PasteSpecial
Workbooks("FirstNoticeEmail.xlsx").Worksheets(1).Range("A7").PasteSpecial Paste:=xlPasteColumnWidths

that is where I lose the ability to see date as a number.
idea how to change this pasting code to save format type

 

[T. Valko]

here seems to be the problem, when I copy and paste the date to a new sheet

Range(Cells(startRow, 1), Cells(endRow, 12)).Activate
Selection.Copy
Workbooks("FirstNoticeEmail.xlsx").Worksheets(1).Range("A7").PasteSpecial
Workbooks("FirstNoticeEmail.xlsx").Worksheets(1).Range("A7").PasteSpecial Paste:=xlPasteColumnWidths

that is where I lose the ability to see date as a number.
idea how to change this pasting code to save format type

Sorry, can't help you with that!