Repeat every month date four times.

Sanjay Gandhi
Sanjay Gandhi used Ask the Experts™
on
Hello,

I want to repeat every date in a month four times. This exercise is required every month. How can I do it easily every month without a macro. As given in the file.

Sanjay.
Month-Dates.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
you may customize this.
Sanjay GandhiFounder, Kenhal
Commented:
Means:
Use regular steps
- insert rows
- copy dates down

or, something else??
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Hi Sanjay,

Check attached, change year and month from drop-down in B1 & B2, get expected result in yellow highlighted cells.
Sanjay_Month_Dates.xlsx
Ryan ChongSoftware Team Lead
Commented:
ooops, i thought i have uploaded mine.

try this as well, another approach.
Month-Dates_b.xlsx
Finance Analyst
Commented:
A simple approach in the attached.

Enter 1st of month in B4 and then this formula in B5 and copied down to row 127 (124 rows for 31 x 4 for each date):

=IF(COUNTIF(B$4:B4,B4)<4,B4,IF(MONTH(B4+1)=MONTH(B4),B4+1,""))

Checking the month of day +1 allows for the months with less than 31 days.

Thanks
Rob
Sanjay GandhiFounder, Kenhal

Author

Commented:
Thanks.
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Hi Sanjay,

Have you tried my solution?

Ryan's solution is just for current month and for Rob's solution you need to enter first date, as per my solution you can select any year or any month and you gets the result.

Anyway, you should have elaborated how both accepted solution helped you even yours you selected as assisted solution.

Attached is using Rob's solution:
Sanjay_Month_Dates_2.xlsx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial