Table most Recent and Oldest Date

[gsandy]

I have a table (tblData) with column of dates (DATE) and wish to find the most recent date and the oldest date in the table. What would the code to do this?
Thanks Sandy

[Roel Jongman]

you can use MAX(tblData[Date]) for newest and MIN(tblData[Date]) for oldest date

[gsandy]

File attached (you will note that the table name has changed).

[gsandy]

Just tried that, it did not like [Date] even though that is the name of the column!
Tried putting in quotation marks - "tblData[Date]" but that didn't work.

[Roel Jongman]

well if you need help on getting the formula exactly right you will have to upload a desensitized example in excel so we can have a go at it.
From your description I cannot think of why it not works, can be more than one thing, not much sense in guessing what it is in your case.

[Roel Jongman]

Ok, that was simple enough..

for some reason your DATE column has a space before and after the text. therefor the formula changes to

=MIN(tblAnalysis[[DATE ]])

- the columnnames in the formula need to be exact to work, therefor with the spaces
- also when a columnheader has spaces in the name you need double marks

[gsandy]

I see you put the code into the worksheet and that worked. I was trying to put the code into a vba module and that is why I couldn't get your code to work.
Do you know if this can be done in vba?

[gsandy]

Did you get that working in my workbook? It doesn't for me!!
Is there any way to remove the spaces in the column header?

[Roel Jongman]

I had some trouble with uploading the attachment.. so that was why it was missing.

see attachement

It looks like it was only 1 space behind DATE
you can remove that space simply by editing the cell C3 (columnheader DATE in your table)

[Roel Jongman]

Aha, I never noticed this post was in the VBA section, so I was not aware you were looking for a VBA solution..

I looked at the code you already had and changed it to work. you were on the right track

Sub MaxMinValue()

    Dim MaxDate As Date
    Dim MinDate As Date

    MaxDate = WorksheetFunction.Max(Sheets("Analysis").Range("tblAnalysis[DATE]"))    
    MsgBox "MaxDate " & MaxDate
    MaxDate = WorksheetFunction.Min(Sheets("Analysis").Range("tblAnalysis[DATE]"))
    MsgBox "MinDate " & MinDate


End Sub

[gsandy]

Thanks Roel, I tested it on dates and it worked.
But had problems with the workbook I am actually working with. The data in tblAnalysis has been transferred from another table and comes through as text, not as dates (a value that I can get a max and min). So I am getting 12:00:00am for both max and min, that seems logical. Even formatting the date column in the table has no effect as the transferred data is just text.
I think a new approach is required. I think about it over night (7.30pm over here). Thanks again for your input.

[gsandy]

Got it. I used the CDate function to convert the "text" date into a "real" date.
Thanks again, cheers Sandy