I would like to ask how to create the sequence formula in order to repeate a date 10 times and do it for the whole year. For example starting from 01/01/2022 to copy this date 10x then for 02/01/2022 to copy it 10x and so on. I started to use following formula for sequence:

=DATE(SEQUENCE(10,1,Year(B1),Month(B1),day(B1))

where B1 is 01/01/2022 and day and month are copied 10 times but the year is changing. Is there a way to do it to have the year same as well? Thanks in advance.

CodePudding user response：

You can take advantage of the fact that a date in excel is, conveniently, an integer number, and do it like that:

=INT((ROW(B1)-1)/10)   $B$1

That'll repeat the date entered in B1 10 times and than switch to next day, using the row number as a guide (so if you are not on the first row, you may need to add X to the formula, where x is the row offset).

(Actual raw integer shown in D column for illustration, repeating every 3 rows instead of 10 to keep the screenshot smaller)