PDA

View Full Version : How to get dates of all mondays between 2 dates?



ppatade242
04-18-2020, 11:45 PM
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
04-19-2020, 03:40 AM
http://www.vbaexpress.com/forum/showthread.php?59469-All-Mondays-in-a-date-range

paulked
04-19-2020, 06:01 AM
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
04-19-2020, 06:08 AM
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
04-20-2020, 04:41 AM
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:
26388 (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:
26389
[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
04-20-2020, 05:03 AM
That's tidy! :clap: