How to obtain most recent date and time from a column

Question

I'm trying to obtain the most recent date and time from a column using VBA excel.

The issue here is that once I run the macro, I obtain the correct most recent date, but the time obtained remains in a 00:00:00 value as shown in the image.

Table with dates and macro result:

How can I obtain the most recent time and date from the B column correctly?

I tried

   Sub ObtainNewestDateTimeofaColumn()

   Max_DateTime = Application.WorksheetFunction.Max(Columns("$B:$B"))         
   Max_Date = Format((CDate((Split(Max_DateTime, ".")(0)))), "dd/mm/yyyy hh:mm:ss")    
   MsgBox (Max_Date)

   End Sub

and obtained the result : "26/02/2022 00:00:00"

The result I was expecting was: "26/02/2022 11:45:00"

Answer 1

The problem is in your Split function. Dates are stored as decimal numbers with the integer part being the number of days since 31/12/1899 and the decimal part being the fraction of a day (or time).

And VBA is US-centric (decimal as dot) in many areas

When you Split on the decimal and return the first element in the resultant array, you are effectively removing the time portion from your datetime value, hence the time is missing from your result.

Change your Max_Date= line to read:

Max_Date = Format(max_datetime, "dd/mm/yyyy hh:mm:ss")

Answer 2

Well the following worked for me. I'm not sure what's going on with your Max_Date = ... line, but just the format should do it.

Option Explicit

Sub MaxValueOutput()
    
    Dim RefRG As Range
    Dim DT_TM As String
    
    Set RefRG = Sheet1.Range("A2:A7")   ' Or your range
    
    DT_TM = Format(Application.WorksheetFunction.Max(RefRG), "yyyy-mm-dd hh:mm:ss")
    MsgBox DT_TM, vbOKOnly, "Demontration Only"
    
End Sub

You might prefer a slightly different number format... But it shouldn't matter.