Help needed to generate & sort 2 lists of dates into months

Hi all,

I'm setting up an excel to produce a calendar and monthly registers for an academy.

Classes may be once or twice a week over a period of 2 to 7 months.

In the attached jpeg you can see that I have set up a course to run on Monday (L) and Wednesday (X) fro the 14th Jan to 24th July.

So my issues are

1) How to create the lists of dates automatically from a given start and finish date.

2) How to sort the list into months (as shown by the arrow) for the whole range.


I'm sure that there is a simple way, but I've been googling and experimenting for the last couple of days with no luck.

Many thanks in advance,

Chris

If you have a start date in B1 and an end date in B2, then you can use these formulae in the cells stated:

B4: =IF(B$1="","",B$1)

B5: =IF(B4="","",IF(B4+7<=B$2,B4+7,""))

You may need to use semicolons ( ; ) in the formulae instead of commas ( , ), and you will probably use SI instead of IF, depending on your regional settings.

Then you can copy the formula from B5 down as far as you need to (until you start to get blanks).

If you need a second set of dates, copy column B into column C, then change C1 and C2 as appropriate.

Hope this helps.

Pete

Hi Pete,

Thanks for the formulae for generating the lists, it worked just as you wrote it.

So ideally now I would like to combine & filter the lists into months, as detailed in question 2 in my original post. Can this be achieved with the Sort & Filter function?

I have seen that there are formulae to calculate the first day of every month and also the first Monday (or Tue, Wed, etc) of every month, so I could probably put something together that uses the formulae above to generate the dates of Tue and Thu for each month.
However, I can see this may turn into a rather long and complex series of IF statements to take into account for all the different combinations.

I'm hoping there is a simpler way.

Thanks in advance,

Chris

It is easier to generate the list of dates going vertically. With the set up as given above, you can put this formula in E4:

=INDEX($B$4:$C$40,INT((ROWS($1:1)-1)/2)+1,MOD(ROWS($1:1)-1,2)+1)

and then copy down until you get blanks - the dates are then combined and in sequence.

Hope this helps.

Pete

P.S. I'll attach the file, so you can see how it gets translated.

Just to finish this off, and put the dates in the month format that you requested in your first post:

I've added this formula to cell F4:

=IF(E4="","",MONTH(E4)&"_"&COUNTIF(F$3:F3,MONTH(E4)&"_*")+1)

and copied this down by double-clicking the fill handle, and then with the month numbers listed in column G, I have this formula in H4:

=IFERROR(INDEX($E:$E,MATCH($G4&"_"&COLUMNS($H:H),$F:$F,0)),"")

this can be copied across and down, as required (I've copied across to column Q, to ensure all dates are picked up in each month).

Hope this helps.

Pete

Hi Pete,

Thanks for your help with this!

You´re a star!


Not sure how to mark this as solved, but for anyone else reading I have the answer I need, Thanks.



Chris

Glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete