VBA: Date Format Issues with Data Validation

[zero269]

Hello,

I've got a macro working that creates a Data Validation drop-down list. I duplicated it to repeat the process for a list of three dates.
However, no matter what I do, they never make it into the Data Validation Source field in the format they show in the VBE.

VBE Shows:

DV Source:

I'm using the following code to build the dates based on the Start Date. Days are then added in a loop using three values in an array (0,7,14).

NOTE:

• StartDate + Days is just adding the three dates into a String.
• StartDate + Days & Format Date ATTEMPT is me attempting to force the format output of the dates... to no avail.

My systems default Short Date is yyyy-mm-dd... which is the output format I'm trying to get.

The other problem is that it's not seen as a date in Excel (E7).
I'm currently using a formula on another sheet to extract those three dates. Then I'm using the Named Range for those three dates for the DV List. Sample data shown at the bottom.
However, I would prefer to use a vba-based solution if possible.
Any help with getting the DV Source field populated with the three dates in yyyy-mm-dd format would be greatly appreciated. From there, I suspect Excel will properly detect it as a Date (ISNUMBER) format.

Create Date Drop-Down List:

Sub Planner_List_Dates()

  'Declarations
  Dim StartDate As Date, i As Integer, sDate As String
  Dim DateList As String: DateList = "" 'Date List
  
  'Get Start Date
  StartDate = Range("A2")
'  StartDate = Format(Range("A2"), "yyyy-mm-dd")

  'Days to Add to StartDate in Loop
  Dim indexArr As Variant
  indexArr = Array(0, 7, 14)

  'Build Drop-Down List Values
  For i = LBound(indexArr) To UBound(indexArr)
  
    'StartDate + Days
    DateList = DateList & StartDate + indexArr(i) & ","

    'StartDate + Days & Format Date ATTEMPT
    'sDate = Format(StartDate + indexArr(i), "yyyy-mm-dd")
    'DateList = DateList & sDate & ","
    
  Next i

  'What's happening here?
  DateList = Mid(DateList, 1, Len(DateList) - 1)
  
  'Add Drop-Down List v1
  With Range("E2").Validation
    .Delete
    .Add _
    Type:=xlValidateList, _
    AlertStyle:=xlValidAlertStop, _
    Formula1:=DateList
  End With
  
End Sub

Sample Data for VBA-based Solution:

VBA Testing.xlsm
	A	B	C	D	E	F	G
1	Start Date		Planner Dates		DV List		+ Days
2	2025-01-06		2025-01-06		1/6/2025		0
3			2025-01-13				7
4			2025-01-20				14
5
6	Date?		Date?		Date?
7	TRUE		TRUE		FALSE
8			TRUE
9			TRUE
Dates_DV

Cell Formulas
Range	Formula
C2:C4	C2	=$A$2+$G2
A7,E7	A7	=ISNUMBER(A2)
C7:C9	C7	=ISNUMBER($C2)

Cells with Data Validation
Cell	Allow	Criteria
E2	List	1/6/2025,1/6/2025,1/6/2025

Current Formula-based Solution:
Excel is gonna crash trying to create the MiniSheet... I'll add it below shortly...

 

[zero269]

OK, so I still can't get a Mini Sheet from my main workbook right now.

Here's the formula that I'm using on a my Lists worksheet to get the three dates:

=CHOOSECOLS(FILTER(t_Planner,(t_Planner[Test Date]=c_Planner_StartDate)+(t_Planner[Test Date]=c_Planner_StartDate+7)+(t_Planner[Test Date]=c_Planner_StartDate+14)),2)

My Planner worksheet is where the drop-down list is located using the Named Range where the above formula is located:

=c_Planner_DateList

Name Manager:
Name: c_Planner_DateList
Comments: Planner dates used for Data Validation on Planner Sheet for filtering books.
Refers To: =Lists!$X$5#
Scope: Workbook

 

[Alex Blakenburg]

I'm afraid I can only confirm that I can replicate that behaviour and the only option would seem to be similar to what you are already doing ie you would need to populate a range with the required dates and use that range as the validation list. I am not sure if there is any benefit in populating that range using VBA as opposed to your current formula method.

 

[zero269]

I'm afraid I can only confirm that I can replicate that behaviour and the only option would seem to be similar to what you are already doing ie you would need to populate a range with the required dates and use that range as the validation list. I am not sure if there is any benefit in populating that range using VBA as opposed to your current formula method.

Hi Alex,

That's what I was afraid of. I know something is broken in Excel considering my differences in results. I don't understand how Excel will produce different results using the same input.
Manual was a GO, while VBA was a NO GO. See VBA Recorded Macro remarks below…

I tried using TEXTJOIN for sh!ts-and-giggles, and I surprisingly got some better results, although not a suitable replacement.

Here's a summary of my comparisons:
VBA code provided below…

VBA Testing.xlsm
	E	F	G	H	I	J	K
1	DV List	Date?	Created	Cell Format	List Format	DV Source	Macro
2	6-Jan	TRUE	Manual + Named Range	dynamic	dynamic	=l_Dates_DV	n/a
3	2025-01-06	TRUE	Manual + Dates	dynamic	static	2025-01-06,2025-01-13,2025-01-20	n/a
4	6-Jan	TRUE	VBA + Named Range	dynamic	dynamic	=l_Dates_DV	Sub DV_Dates_v1()
5	2025-01-06	TRUE	VBA + TEXTJOIN	dynamic	static	45663,45670,45677	Sub DV_Dates_v2()
6	1/6/2025	FALSE	VBA Recorded Macro	n/a	n/a	1/6/2025,1/13/2025,1/20/2025	Sub DV_Dates_v3()
7	1/6/2025	FALSE	VBA + String of Dates	n/a	n/a	1/6/2025,1/13/2025,1/20/2025	Sub DV_Dates_v4()
Dates_DV

Cell Formulas
Range	Formula
F2:F7	F2	=ISNUMBER(E2)

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=l_Dates_DV
E3	List	2025-01-06,2025-01-13,2025-01-20
E4	List	=l_Dates_DV
E5	List	45663,45670,45677
E6:E7	List	1/6/2025,1/13/2025,1/20/2025

Summary of my Analysis:

• Manual + Named Range is obviously the optimal solution until someone can figure out why Excel is broken when using VBA to achieve the same results.
• Manual + Dates works, but I lose the dynamic number formatting in the drop-down list.

The 1st and 3rd options provide the best results that use a Named Range. Mainly because the drop-down list will display the same Number Format as the source data in the Named Range. This has always been a nice option for me; only because I'm never looking at the year when selecting a date; and the column is more narrow... as preferred.

Manual + Named Range is obviously the optimal solution until someone can figure out why Excel is broken when using VBA to achieve the same results.

'VBA + Named Range
Sub DV_Dates_v1()
  Range("E4").NumberFormat = "[$-en-US]yyyy-mm-dd;@"
  With Range("E4").Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="=l_Dates_DV"
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
  End With
End Sub

VBA + TEXTJOIN is really the only VBA option where storing the dates is not the preferred approach. This one displays the Date Serial number in the drop down list. This is certainly not acceptable for 99% of use cases. However, for me, I could easily work with this considering I only have three dates in the list… one week apart from the other. Treated as Week 1, Week 2 and Week 3. So, it's not confusing in this one instance.

'VBA + TEXTJOIN
Sub DV_Dates_v2()
  Range("E5").NumberFormat = "[$-en-US]yyyy-mm-dd;@"
  With Range("E5").Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:=WorksheetFunction.TextJoin(",", True, Range("C2:C4"))
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
  End With
End Sub

VBA Recorded Macro: I recorded a macro performing the manual steps that I'm currently using. However, if I run the code, it produces dates in the Source field and Excel rejects it as a Date. So, not useable.

'Recorded Macro
Sub DV_Dates_v3()
  Range("E6").NumberFormat = "[$-en-US]yyyy-mm-dd;@"
  With Range("E6").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="2025-01-06,2025-01-13,2025-01-20"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
  End With
End Sub

VBA + String of Dates: This one produces the same results as the one above. String values; not usable dates.

'VBA + String of Dates
Sub DV_Dates_v4()
  Dim Dates As String: Dates = "2025-01-06,2025-01-13,2025-01-20"
  Range("E7").NumberFormat = "[$-en-US]yyyy-mm-dd;@"
  With Range("E7").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=Dates
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
  End With
End Sub

Thanks Alex, and best regards,