Excel: How to increment time once in every 1000 rows-CodePudding

I have a date-time column in DD-MM-YYYY HH:MM:SS format. I need to increment this column value by 10 minutes once in every 1000 rows. Once an increment has been done, the value will remain constant for 1000 rows after which another increment will be made.

I tried to use the standard increment option by using time function, but its available for either time or date but not for both

=TIME(HOUR($L$1),MINUTE($L$1),SECOND($L$1) INT((ROW()-1/1000)))

I also tried to use MOD function, but it is throwing #VALUE! error on 15-06-2022 01:00:00 cell value

=x1 IF(MOD(ROW()-ROW(A$1),1000)=0,10,0)

Can you please help in this regard

CodePudding user response：

Simpler (and more robust than using row() if anybody inserts rows at the top) is:

in A2 put =A1,
drag down as far as A1000
in A1001 put =A1 10/60/24
drag down as far as you need