How to get list of in-between dates in dd-mmm-yyyy fromDate and ToDate

[NimishK]

Hello

How can i list down the in-between dates in (dd-mmm-yyyy) format from below textboxes ie txtFromDate.text and txtToDate.Text
also the list to contain the dates txtFromDate.Text and txtToDate.text with in-between dates

Option Explicit
Dim fromDate As Date, toDate As Date

Private Sub txtFromDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If txtFromDate.Value = vbNullString Then
    Exit Sub
ElseIf Not IsDate(txtFromDate.Value) Then
    Cancel = True
    MsgBox "Invalid date, please re-enter", vbCritical


    txtFromDate.Value = vbNullString
    txtFromDate.SetFocus
    'MsgBox "Invalid date, please re-enter", vbCritical
    Exit Sub
End If
fromDate = DateSerial(Year(Date), Month(Date), Day(Date))
txtFromDate.Value = Format(CDate(txtFromDate.Value), "dd-mmm-yyyy")

End Sub

Private Sub txtToDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If txtToDate.Value = vbNullString Then
    Exit Sub
ElseIf Not IsDate(txtToDate.Value) Then
    Cancel = True
    MsgBox "Invalid date, please re-enter", vbCritical


    txtToDate.Value = vbNullString
    txtToDate.SetFocus
    'MsgBox "Invalid date, please re-enter", vbCritical
    Exit Sub
End If
toDate = DateSerial(Year(Date), Month(Date), Day(Date))
txtToDate.Value = Format(CDate(txtToDate.Value), "dd-mmm-yyyy")

End Sub

Thanks NimishK

 

[James006]

Hello,

An Array Formula could do the job for you ...

With you Start Date in cell A2 and your End Date in cell A3 ...

In cell B2, you could test following:

=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDIRECT(MIN($A$2:$A$3)&":"&MAX($A$2:$A$3))),$A$2:$A$3,0)),ROW(INDIRECT(MIN($A$2:$A$3)&":"&MAX($A$2:$A$3)))),ROWS($1:1)),"")

Then you can copy formula down ... to get your list

Hope this will help

 

[sandy666]

you can try PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]FromDate[/td][td=bgcolor:#5B9BD5]ToDate[/td][td][/td][td=bgcolor:#70AD47]Days[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

01/01/2019​

[/td][td=bgcolor:#DDEBF7]

10/02/2019​

[/td][td][/td][td=bgcolor:#E2EFDA]

01/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

02/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

03/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

04/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

05/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

06/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

07/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

08/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

09/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

10/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

11/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

12/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

13/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

14/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

15/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

16/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

17/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

18/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

19/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

20/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

21/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

22/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

23/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

24/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

25/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

26/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

27/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

28/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

29/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

30/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

31/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

01/02/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

02/02/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

03/02/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

04/02/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

05/02/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

06/02/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

07/02/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

08/02/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]

09/02/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

10/02/2019​

[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Inserted Time Subtraction" = Table.AddColumn(Source, "Subtraction", each [ToDate] - [FromDate], type duration),
    #"Calculated Total Days" = Table.TransformColumns(#"Inserted Time Subtraction",{{"Subtraction", Duration.TotalDays, type number}}),
    #"Added Custom" = Table.AddColumn(#"Calculated Total Days", "Days", each List.DateTimes([FromDate],[Subtraction]+1,#duration(1,0,0,0))),
    #"Expanded Days" = Table.ExpandListColumn(#"Added Custom", "Days"),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Days",{"Days"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Days", type date}})
in
    #"Changed Type"[/SIZE]

 

[NimishK]

Guys thanks for your inputs.
James006 for your below code require VBA syntax

=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDIRECT(MIN($A$2:$A$3)&":"&MAX($A$2:$A$3))),$A$2:$A$3,0)),ROW(INDIRECT(MIN($A$2:$A$3)&":"&MAX($A$2:$A$3)))),ROWS($1:1)),"")

Sandy666
Power Query have really not worked. And wanted the help in simple VBA. if you have solved the same without power query or before power query was launched would really appreciate

 

[sandy666]

I left vba in favor of power query so I can't help you

have a nice day

 

[James006]

Hello,

The VBA equivalent can be easily achieved ...

Just turn on your macro recorder and input the formula ...

HTH

 

[FormR]

Hi, here is an alternative you could try - this lists the dates starting in cell A1 of the active sheet downwards and is attached to command button 1.

Private Sub CommandButton1_Click()
Dim i As Long
i = DateValue(txtToDate) - DateValue(txtFromDate)
With Range("A1").Resize(i + 1)
    .Value = Evaluate("datevalue(""" & txtFromDate & """) + (row(1:" & i + 1 & ")-1)")
    .NumberFormat = "dd-mmm-yyyy"
End With
End Sub

 

[NimishK]

FormR
Excellent One. You have Formed exactly to what i wanted. Just The perfect one.
Something new i.e Evaluate i've learnt Today.

James006 will also add into Macro Recorder to turn on the Formula. Not yet tried. Thanks
Sandy666 No isssues but will surely keep in mind your formula when i wiill work on Power Query. Thanks Anyways

Thanks Guys
NimishK