Dates of Sundays only

Please see attached file with Sundays for 2014

Originally Posted by Nardar

Wow! Thanks for the quick response. A couple of questions:

1. Hiding the first column won't mess anything up, will it?

2. For 2015, do I just search for 2014 and replace with 2015?

Again, I appreciate your very quick help!

You're welcome

Hiding first column is fine, just don't delete it.

for 2015, just replace dates in column A and the formula will pick Sundays.

Please don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

well you could if you want to use that array formula without a helper put 1st of year in say b1
as 1/1/2014
then in a2 array entered and filled down

=IFERROR(SMALL(IF(WEEKDAY(ROW(INDIRECT($B$1&":"&$B$1+366)))=1,ROW(INDIRECT($B$1&":"&$B$1+366)),""),ROWS($A$1:A1)),"")
or alteratively
put year in a1 as 2014
a2 = =DATE($A$1, 1, MOD(7-DATE($A$1, 1, 1)+ 1,7)+1)
a3 = =IF(YEAR(A2+7)>$A$1,"",A2+7) fill down to a54 (there can be a maximum of 53 sundays in a year)

hi Nardar. from your opening post, it doesn't sound like you are extracting all the Sundays from a list of dates? it sounds like you just want all the Sundays of 2014?
how about you just type the 1st Sunday date in A2 as 5Jan2014 or use a formula:
=WORKDAY.INTL("31dec2013",1,"1111110")
or:
="31dec2013"-WEEKDAY("31dec2013",2)+7

you could also put the 1Jan2014 in say B1 & then use:
=WORKDAY.INTL(B1-1,1,"1111110")
=B1-1-WEEKDAY(B1-1,2)+7

for the rest of the cells, simply use:
=IFERROR(IF(YEAR(A2+7)=YEAR(A2),A2+7,""),"")

change B1 to the year you want to change all the results. Martin is referring to Column A of the files above as the helper column. if you use the formulas we do, you don't have to create Column A & hide it. i created 4 columns inside for your choice. you can pick any of them

We all know Excel start to count date from 01/01/1900, stored as 1. It was Sunday.
To state the last Sunday of any date (i.e 1/1/2014) we use:
=INT(DATE(2014,1,1)/7)*7+1
and the next Sunday:
=INT(DATE(2014,1,1)/7)*7+8

Therefore, to create a list of Sundays of 2014 :
A1=INT(DATE(2014,1,1)/7)*7+8
A2=A1+7
Drag down

Hi bebo,

I wish all machines would use 1/1/1900 as the start day of our systems. It seems Apple or some other OS's liked 1904 instead.

When you assumed in "We all know" in your post above, your assumption was wrong if you include both PC and Apple OS.

MarvinP,
You are right that some machines start to count from 1904.
I am not familiar with 1904 OS and wondering if the formula cound work or not.

@Nardar a helper column/row is any row or column of data that you add in to help you get the data you need
in that case the helper column was the list of all days in 2014