I have a long spreadsheet (over 4700 rows) with blocks of 48 rows per day. How can I automate populating the next block of rows with the date of the next day?
i.e 48 rows with date 01/09/2025, then 48 rows with 02/09/2025 etc…
Thanks @Lupp @karolus @LeroyG @koyotak @EarnestAl @fpy
for your generous help. Times like these the internet and its community shows its best side!
1 Like
1 Like
Building over @Villeroy shoulders:
D7: = D$6 +INT((ROW()-ROW( D$6 ))/48), where:
D$6 = first date cell, and
48 = block size.
ROW([ref]) accepts a reference as optional argument: = D$6 +INT((ROW(A1)-1)/48)
Start with (obviously) the start_date, and in the cell below:
=cell_above + QUOTIENT(SEQUENCE(5000);48)
2 Likes
See also:
ask129768_alt.ods (79.2 KB)
There is a solution which avoids the need to lock an output range and the usage of a helper column as well.
1 Like
OP did NOT ask for increment by 30 Minutes, but to repeat each date 48 times!
INT(DATE +1/48) should work.
1 Like
That’s your failure!
The point is a result, not hairsplitting. The OP is looking for DATE, not TIME, which, with +0.5 hours, is far more ingenious than the convoluted use of indirect formulations. Therefore, your algorithm is not implied in the problem statement!
Please avoid/suppress your condescending arrogance, which will only result in me no longer offering any solutions!
1 Like
But there could be problems when sorting by date and another criteria.
In that case when column is completed, copy it and paste as unformatted text and set column to suitable date type (DMY,MDY,YMD) in the Text Import wizard
1 Like
Indeed.
There is a little flaw, however: The date cells do not contain only the date, but also a time part which -bad effect of number formatting as it is- only is suppressed for the view.
See also:
ask129768_simplicissimus_070732_commented.ods (29.5 KB)