excel - How to get the day of every Monday in a month for a given date -

let me start following example:

a1 =date(2015, 6, 1) a2 =a1 + 7 

result

6/8/2015  6/15/2015 6/22/2015 6/29/2015 7/6/2015 7/13/2015 7/20/2015 

good result, i'd more formatted sequence like:

a1 =text(day(date(2015, 6, 1)), "dd") a2 =text(day(a1 + 7), "dd") 

expected:

01 08 15 22 29 06 13 20 

result:

31 05 10 15 20 25 30 04 

why , how fix it?

ps: i'm using google stylesheets

you've stumbled common mistake. current formula a1 gets day of 01-jun-2015. integer, not date; 1. should not using dd format mask. either discard day function or use 00 format mask.

=text(day(date(2015, 6, 1)), "00") =text(date(2015, 6, 1), "dd") 

your =text(day(a1 + 7), "dd") confuses date integer representing day of month. while work single month (by sheer coincidence due similarity month jan-1900) cause problems next month; e.g. jun-2015 has 30 days , jan-1900 has 31 days.

best start date in a1 , expand date according number of rows fill down.

=text(date(2015, 6, 1+(row(1:1)-1)*7), "dd") 

btw, excel thinks 01-jun-2015 42,156, not 1.