Column of Sunday Dates

[BigJohnW]

Hi,

I hope someone can help.
I have a spreadsheet i use to record mileage and dates. I have a column with dates A2:A366 and each date is in the format
1st January
2nd January
3rdJanuary
etc down to the end of December. Then there are columns (currently B:E) which hold annual mileage. So As I do the miles, I enter the value in to the cell for the correct date and year. So mileage on 1st January 2013 goes into cell E2, 5th January 2012 is in D6, etc.
The Cells are arranged in a table, and I have various functions and macros running in VBA to use the data as I enter it in.
I want to create a second table, on a different sheet. This table will have dates in the left hand column, and then a column for weekly mileage and another for weekend mileage. I will create code which will take values from the first table and populate the second and third column in the second table with calculated values for the miles covered in a week and a weekend, simple maths calculations, which I can manage.

What I would like to do is create a macro/function that will populate the first column of the new table with the date of every Sunday of the current year, in the format 6th January, 13th January, 20th January etc right through until the end of December. I would like the format of the Date column in the second table to match the format in the thirst so that once I have a selected date in the second table, I can loop through the entries in the first table until the two match. I canthen select values in the miles columns to do the various calculations.
I hope that all makes sense??
I’m struggling with the macro/function that enters the Date of each Sunday of the current year into the 2nd Table. That’s the first step. The second is to then convert it into the same format as the first table (1st January, Second January etc.

Please can anyone advise? I’ve tried all sorts using weekday and various other things, but I can’t get it. I’ve tried ranges and all sorts but I can’t get the hang of it. I’ve done basic VBA but I’m not very experienced with it.
Many Thanks
John

[dilipandey]

Hi John,

I would like the format of the Date column in the second table to match the format in the thirst so that once I have a selected date in the second table, I can loop through the entries in the first table until the two match.

Look alike format does'nt mean that values are same.. one could be text and another could be DATE.
Also, I can give you a macro which will fetch the sundays of the current year, in the row which you select - if that suits you and you are comfortable customizing that as per your need ELSE upload a sample where you want these Sundays to appear

Regards,
DILIPandey
<click on below * if this helps>

[Jakobshavn]

To make a column of Sundays, In A1 enter:
41280
In A2 enter:
=A1+7 and copy down thru A52

Then format A1 thru A52 as Number > Custom > d " of " mmmm

This should display:

6 of January
13 of January
20 of January
27 of January
3 of February
10 of February
17 of February
24 of February
3 of March
10 of March
17 of March
24 of March
31 of March
7 of April
14 of April
21 of April
28 of April
5 of May
12 of May
19 of May
26 of May
2 of June
9 of June
16 of June
23 of June
30 of June
7 of July
14 of July
21 of July
28 of July
4 of August
11 of August
18 of August
25 of August
1 of September
8 of September
15 of September
22 of September
29 of September
6 of October
13 of October
20 of October
27 of October
3 of November
10 of November
17 of November
24 of November
1 of December
8 of December
15 of December
22 of December
29 of December

[BigJohnW]

Hi Gents, Thanks for replying.

Dilipandey, many thinks, you were right, the formatting was different - one was Date, the other was General, but I'd appreciate the macro if you wouldn't mind posting it, so that I can look at it and learn from it.

Jakobshavn, many thinks for the simple and elegant solution. I was looking to do something more complicated and making it more difficult than it should have been!!!
Thanks

John

[dilipandey]

You are welcome John..

for fetching sundays, you can use Jakobshavn's method but if you need macro, consider my point as I posted earlier:-

ELSE upload a sample where you want these Sundays to appear

Regards,
DILIPandey
<click on below * if this helps>

[Marcol]

Another possibility might be this.
In say, B1, select the year required from the drop-down.

In B2, drag Down as far as you need.

Please Login or Register  to view this content.

Select another year to see the changes