Multiple dates in one cell automatically

[tyr443]

Hi all,

I want to fill a single cell with dates, spaced by 7 from the input date until the end of the month.
For example: I enter 02/01 (any format is fine) and the cell should fill with 02/01 09/01 16/01 23/01 30/01

Is this possible?

Thanks in advance for any help, sorry if this is a stupid question I am not very familiar with excel, I have been a MATLAB user until now.

 

[Rowrow]

Howsit dude, this should work:


Function DaysToEOM(dte As Date) As String
Dim s As String, n As Integer
n = Month(dte)
Do Until Not n = Month(dte)
s = s & Format(dte, "dd/mm") & " "
dte = DateAdd("d", 7, dte)
Loop
DaysToEOM = Trim(s)
End Function

 

[tyr443]

At the risk of sounding stupid.

How do I go about using this?

 

[tyr443]

Okay I think I worked it out but it doesn't seem to work. I get an error message every time.

 

[Rowrow]

Okay I think I worked it out but it doesn't seem to work. I get an error message every time.

What error message are you getting?

Paste the script into a module in your Visual Basic window.
If you had a date in cell A1, and you wanted the string in cell B1, in cell B1 type: =DaysToEom(A1)

 

[tyr443]

#NAME? is the error.

In A1 I put 01/03

B1 I put: =DaysToEOM(A1)

 

[James006]

Hi,

You can test the following event macro ... (it goes in the Sheet module)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Dim i As Integer
Dim max As Integer
max = Day(DateSerial(Year(Target), Month(Target) + 1, 1) - 1)
max = (max - Day(Target)) / 7
  i = 1
  Do
    Target.Offset(i, 0) = Target + 7 * i
    i = i + 1
  Loop Until i = max + 1
End Sub

HTH