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

[mehmetcik]

Famous last words but that is quite simple

Can you give me more information?

Is this for a specific cell, row or column?

Right Click on your sheet name at the bottom excel and select view code.

Paste this code in the macro window and close it.

type 2/3 into cell B4.

Please Login or Register  to view this content.

[tyr443]

Thanks for getting back to me so quickly.

Basically I need a formula that I can put in any cell.

So for example, I type the number of days in the month (31) and then the starting point 03/01 and then have an output of all dates within that month including the start date

So the output in this example would be: 03/01, 10/01, 17/01, 24/01 << All in one cell.

The reason for this is that my job involves reoccuring appointments spaced a week apart and from this cell I will then be counting the number of dates (4 in this case) and multiplying by 2 in a different cell, this will then boil down my working hours.

I hope this makes sense, please feel free to ask more if you need to.

[tyr443]

I should also mention that I was given this, but I cant get it to 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

[dosydos]

assuming your start date is in cell A1, and the number of days in the month are in B1 then enter this formular whereever:


=TEXT(A1,"mm/dd") & IF((A1+7)>EOMONTH(A1,0),"",", " & TEXT(A1+7,"mm/dd")) & IF((A1+14)>EOMONTH(A1,0),"",", " & TEXT(A1+14,"mm/dd")) & IF((A1+21)>EOMONTH(A1,0),"",", " & TEXT(A1+21,"mm/dd")) & IF((A1+28)>EOMONTH(A1,0),"",", " & TEXT(A1+28,"mm/dd"))

[mehmetcik]

This will work in any cell.

Please Login or Register  to view this content.

[tyr443]

Thats fantastic!

Follow up question being, how to I count the number of dates in that cell now?

[tyr443]

Could you tell me how to use that please Mehmetcik.

I assume it is a function so I add it in the visual basic editor.

Beyond that I am pretty useless.

[mehmetcik]

This is a sheet specific macro.

So you paste it into the sheet macro sheet as described above.

Right click on the sheet name and select view code. Paste the code into the new window and close it.
Enter 2/3 in any cell.

*************************************************************************************

Please explain where you would want to count the number of dates in the cell?

Please Login or Register  to view this content.

This formula gets rid of the spaces and commas.

Whats left are dates formatted dd/mm so divide by 5 to find how many dates.

*************************************************************************************

[dosydos]

Assuming dates are in D1

=LEN(D1)-LEN(SUBSTITUTE(D1,"/",""))

[tyr443]

Thank you very much you have both helped a lot!

[tyr443]

Out of interest Mehmetcik, is it possible to change that code in to a formula?
So I can enter it in specific cells.

No is fine, just wanted to know.

[mehmetcik]

It formula is a lot harder because you would need to imitate a loop but it is possible.

Also you would need to use two cells.

If you want to use specific cells then the macro can be modified quite simply.

You could colour the cells a specific color or insert a comment, or list it in variable in the macro.
The options are endless.

Please Login or Register  to view this content.