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…
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.
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!
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