Find lowest date and display cell ref.

Sinbad

Board Regular
Joined
Apr 18, 2012
Messages
224
Hi All,

need some help.

we have a bunch of books that are listed in Col A. Colb will be simple in and out selected from a dropdown.
I am using a little code to set a date in Col C depending on what is selected in Col B. If the book is "in" then the date in Col C is cleared, if the book is out, Col C will show the date it left. This works fine
Code:
        trg = Target.Address
        Vab = ActiveSheet.Range(trg).Value
        If Vab = "Out" Then
        ActiveCell.Offset(0, 1) = Format(Now(), "mm.dd.yyyy")
        Else
        ActiveCell.Offset(0, 1).Value = ""
        End If

now to my problem.
I would like to find the oldest date in Col C. Problem here is that there are blank cells and my code always returns the wrong date.

Code:
Dim lRow As Long
    
    lRow = Cells(Rows.Count, 2).End(xlUp).Row
    LowDate = Format(WorksheetFunction.Min(Range("C2:C" & lRow)), "mm.dd.yyyy")
    
   Debug.Print Format(LowDate, "mm.dd.yyyy")
        
    Cells.Find(What:=Range(LowDate), After:=ActiveCell, LookIn:=xlValues, LookAt _
    :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate

Ultimately I would like the lowest date copied to cell H2 and the ref. to which one it is into cell H4 (row number)

I only have 2 hair left and woul dlike to keep them. Any takers?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Hi,

To me blank cells made no problems but a value=0 does. So I decided to check if the cell was a date to consider it.
Code:
Sub LowDate()
Dim lRow As Long
Dim dCell As Range
Dim dRow As Long
Dim lDate As Date
 lDate = "31 / 12 / 2999"
 lRow = 0
lRow = Cells(Rows.Count, 2).End(xlUp).Row
For Each dCell In Range("C2:C" & lRow)
    If (IsDate(dCell) = True And dCell < lDate) Then
      lDate = dCell
      dRow = dCell.Row
    End If
Next dCell
   If lDate = "31 / 12 / 2999" Then
        Range("H2") = ""
        Range("H4") = ""
        Else
        Range("H2") = lDate
        Range("H4") = dRow
   End If
End Sub

Hope it will save both hairs :)
 
Last edited:
Upvote 0
Hi,

neat. One problem though is the formatting of the date.

I got no results and decided to have it show me all the comparison it does. Your date being compare is in the format
31-Dec-2999 whilst the dates in the cells are formatted 05.29.2018. this leads to nothing being returned.

I tried adjusting your, but somehow it does not like what I do.

Any suggestions on that?
 
Upvote 0
And if you put ldate = 1/1/2999 or 1.1.2999 or ldate= format ("31 / 12 / 2999","mm.dd.yyyy")?

If not, then maybe
Code:
[LEFT][COLOR=#333333][FONT=monospace]Sub LowDate()
Dim lRow As Long
Dim dCell As Range
Dim dRow As Long
Dim lDate As Date
 lDate = "31 / 12 / 2999"
 lRow = 0
lRow = Cells(Rows.Count, 2).End(xlUp).Row
For Each dCell In Range("C2:C" & lRow)
    If (IsDate(dCell) = True And format(dCell,"dd/mm/yyyy") < lDate) Then
      lDate = [COLOR=#333333][FONT=monospace]format(dCell,"dd/mm/yyyy")[/FONT][/COLOR]      
      dRow = dCell.Row
    End If
Next dCell
   If lDate = "31 / 12 / 2999" Then
        Range("H2") = ""
        Range("H4") = ""
        Else
        Range("H2") = format(lDate,[COLOR=#333333][FONT=monospace]"mm.dd.yyyy")[/FONT][/COLOR]        
        Range("H4") = dRow
   End If
End Sub[/FONT][/COLOR][/LEFT]
?

<strike></strike>
 
Last edited:
Upvote 0
Hi,

I tried "lDate = Format(lDate, "mm.dd.yyyy")", obviously wrong. Your "lDate = Format("31 / 12 / 2999", "mm.dd.yyyy")" gives a type mismatch. Will try the new code.

But Thank you so far, both hair still safe :rofl:
 
Upvote 0
Ohhhh... WUNDERFUL..

Second code works like a charm. clever. Learnt something new.

Thank you.
 
Last edited:
Upvote 0
You are welcome.

Seems VBA works in dd/mm/yyyyy format, which is strange since made in US.

Thank you for the feedback :)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top