How to get dates of all mondays between 2 dates?

[ppatade242]

Hello all,

Can anyone help me in getting an user define function to get monday dates between two dates?

For Ex: Start date = 4/4/2020 & End date= 5/5/2020, then result should give me a list of Mondays dates

dd/mm/yyyy
dd/mm/yyyy
dd/mm/yyyy
......so on

Please help me this is very urgent..

Thank u in advance.

[paulked]

http://www.vbaexpress.com/forum/show...n-a-date-range

[paulked]

I had a bit of time so my effort:

Start date in A1, End date in A2 and Day you want in A3

Sub GetDays()
    Dim i As Long, j As Long, ar, dy As Long, dys() As String
    dys = Split("su,mo,tu,we,th,fr,sa", ",")
    For i = 0 To 6
        If dys(i) = LCase(Left(Cells(3, 1), 2)) Then dy = i + 1
    Next
    If dy = 0 Then MsgBox "Invalid day!": Exit Sub
    ReDim ar(0)
    For i = Cells(1, 1) To Cells(2, 1)
        If Weekday(i) = dy Then
            ar(j) = i
            j = j + 1
            ReDim Preserve ar(j)
        End If
    Next
    Range("c2").CurrentRegion.ClearContents
    Range("c2:c" & j + 1) = WorksheetFunction.Transpose(ar)
End Sub

[Paul_Hossler]

Hello all,

Can anyone help me in getting an user define function to get monday dates between two dates?

For Ex: Start date = 4/4/2020 & End date= 5/5/2020, then result should give me a list of Mondays dates

dd/mm/yyyy
dd/mm/yyyy
dd/mm/yyyy
......so on

Please help me this is very urgent..

Thank u in advance.

It's tricky getting a UDF (a WS function) to return an array with a varying number of elements

Are you sure that you a UDF, and not a sub like paulked's?

Maybe a sample workbook with the expected result will help

[p45cal]

It's tricky getting a UDF (a WS function) to return an array with a varying number of elements
Are you sure that you a UDF, and not a sub like paulked's?

Yes, it's tricky. In the attached there's a UDF called Mondays which is used thus:
2020-04-20_122929.jpg (I used Nd for End because End is a reserved word.)

It is committed to the sheet using Ctrl+Shift+Enter (rather than just Enter) and usually to a range of cells in one go. That range of cells can be a row or a column.
I've handled the varying number of elements with a modicum of trial and error; If you've entered the formula into a range which is too large, you get the usual error cells at the end of the range, if the range is too small, the UDF reports how many more cells you need in the last cell:
2020-04-20_123635.jpg
[You could enter the formula into a single cell (no need even to array-enter for a single cell) and it will tell you how many more cells you need.]

You have to remember this is a formula, so if you change the values in cells B1:C1 you'll get different results straight away, so if you need to keep these data then you should Copy, Paste-Special, Values, in situ.

[paulked]

That's tidy!