Compare and Find Date

[mhb0825]

I have a spreadsheet where A1 contains today's date and columns C1 thru PH129 contains weekly increments (dates) i.e. 04/04/2011, 04/11/2011. I would like compare today's date with those dates and determine which column would be the first date that is greater than today. I would eventually like to automatically "scroll" to this date but for now just want to start with the first question. Thanks for the assistance!

 

[T. Valko]

I have a spreadsheet where A1 contains today's date and columns C1 thru PH129 contains weekly increments (dates) i.e. 04/04/2011, 04/11/2011. I would like compare today's date with those dates and determine which column would be the first date that is greater than today. I would eventually like to automatically "scroll" to this date but for now just want to start with the first question. Thanks for the assistance!

Is the date range really C1 thru PH129 or is it actually C1 thru PH1?

 

[texasalynn]

Welcome to MrExcel board....

wondering if your meant PH1 instead of PH129

Sub CompareDates()
    x = Date
    y = Application.Match(CLng(x), Range("A1:PH1")) + 1
    MsgBox "Next nearest date in Column " & y
End Sub

 

[mhb0825]

Oops! Yes it is PH1, PH129 is the last cell containing a value in the spreadsheet. Thanks for the help!

 

[T. Valko]

Oops! Yes it is PH1, PH129 is the last cell containing a value in the spreadsheet. Thanks for the help!

OK, try this array formula**:

=INDEX(C1:PH1,MATCH(TRUE,C1:PH1>A1,0))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[mhb0825]

Thank you so much, that works great! I really appreciate your expertise!!This spreadsheet is rather lather and cumbersome for scrolling and viewing contents of columns and will actually extend beyond PH1 as time goes on. Is it possible to, based on the date found, then "go to" the cell that contains the date (would be in C1 to Ph1)? Maybe a button?? Hope this makes sense.

 

[T. Valko]

Thank you so much, that works great! I really appreciate your expertise!!This spreadsheet is rather lather and cumbersome for scrolling and viewing contents of columns and will actually extend beyond PH1 as time goes on. Is it possible to, based on the date found, then "go to" the cell that contains the date (would be in C1 to Ph1)? Maybe a button?? Hope this makes sense.

How about a hyperlink?

Enter this array formula** in A2:

=HYPERLINK("#"&ADDRESS(1,MATCH(INDEX(C1:PH1,MATCH(TRUE,C1:PH1>A1,0)),1:1,0)),"Goto Date")

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Adjust for the end of range PH1.

 

[mhb0825]

Kudos! Works perfectly. Although it's not that difficult to scroll, this will save so much time, as I am in this workbook often. Thanks again.

 

[T. Valko]

Kudos! Works perfectly. Although it's not that difficult to scroll, this will save so much time, as I am in this workbook often. Thanks again.

You're welcome. Thanks for the feedback!