4

I have a set of dates pulled from a PivotTable (which can either be accessed straight from the PT or I can copy it into a new sheet - I'm not fussed either way) and I need both the earliest and latest date e.g.

Scheduled date
01/01/17
05/08/17
08/11/16
03/12/16
...

So all I want is for VBA to tell me that the earliest date is 08/11/16 and the latest is 05/08/17 (This is part of a macro that cycles through different versions of different assessments, so a manual fix won't suffice and the model is so large that an array formula in the sheet is going to make it ridiculously slow).

So far I have tried the following for the earliest date:

Dim AllDates As Variant
Dim NumberDates As Integer
Dim Earliest As Date, Latest As Date

Set AllDates = ThisWorkbook.Sheets("Pivot Table 5 - To Use").Range("A4:A203")

'Attempt 1
Earliest = WorksheetFunction.Min(AllDates)

'Attempt 2
Earliest = Format(Application.Min(AllDates), "dd/mm/yyyy")

'Attempt 3
Earliest = Format(WorksheetFunction.Min(AllDates), "dd/mm/yyyy")

'Attempt 4
Debug.Print CDate(Application.Max(AllDates))

Every attempt results in "00:00:00" so I'm assuming I am actually working with Date values (I doubled checked and ensured that the worksheets were formatted to the correct date format) but beyond that I am COMPLETELY stumped.

halfer
  • 19,824
  • 17
  • 99
  • 186
UncouthScientist
  • 55
  • 7
  • When you say every attempt results in "00:00:00", do you meanEarliest and latest? both of them? Also, can you have access to original source of data that fetches your PivotTable? Try do get the earliest and latest in that data, instead of PivotTable. – Foxfire And Burns And Burns Apr 12 '18 at 12:01
  • Are there any blank values in the range `A4:A203`? – jsheeran Apr 12 '18 at 12:04
  • `CDate(Application.Min(AllDates))` works for me fyi. Maybe it is related to regional settings – Tehscript Apr 12 '18 at 12:06
  • 1
    If you are getting 0 for Min **and** Max, then your dates are stored as text in the pivot table. – Rory Apr 12 '18 at 12:21
  • This is really weird, I'm having the same issue while reproducing but it's not consistent... – dwirony Apr 12 '18 at 12:24
  • Okay @Rory's right, they're definitely being stored as text – dwirony Apr 12 '18 at 12:30
  • @FoxfireAndBurnsAndBurns I get the result of 00:00:00 for both Min and Max functions. I have used the source data and get the same issue. When I look at the cell formatting it *claims* to be a date in the format of dd/mmm/yyy – UncouthScientist Apr 12 '18 at 12:36
  • @Rory Any suggestions of how to reformat the data into what I need? I ask because I'd have assumed that Attempts 2 & 3 should have helped... – UncouthScientist Apr 12 '18 at 12:37
  • If all else fails, you could use `For Each rCell In AllDates` and do a manual Min/Max check with `cDate`? – Chronocidal Apr 12 '18 at 12:41
  • 3
    Cell formatting doesn't affect what's actually in the cells. Select the date column, choose Data - Text to Columns, select Delimited, then in the next step clear all the delimiters and then in the final step choose Date and specify the order (DMY, MDY whatever), then press Finish. That should convert them to true dates. – Rory Apr 12 '18 at 12:43
  • @Rory Thank you so much! Now to automate that... – UncouthScientist Apr 12 '18 at 12:52

1 Answers1

0

This worked for me:

Sub test()

Dim Earliest As Date
Dim Latest As Date

AllDates = "$A$2:$A$204"

Earliest = Application.WorksheetFunction.Min(ActiveSheet.Range(AllDates))
Latest = Application.WorksheetFunction.Max(ActiveSheet.Range(AllDates))

MsgBox Earliest & Chr(13) & Latest

End Sub