Generate a sequence of datetimes between 2 times

user_5549354 0
Viewed 33

I am generating the timestamps in a day to send some messages to customers

Make a copy of sheet here: https://docs.google.com/spreadsheets/d/1a1-tVXxlZI4RlNxH4sIEejZhh0FKUFZ-jV88M8s-agE/copy

They are to be sent from Start time to End time and they cannot be sent after End time (My customers sleep after End time)

I have created a formula that can generate the sequence but it will not consider end time and keeps going continuously.

=arrayformula(text(SEQUENCE(200,1,value((datevalue(date(D2,C2,B2)) + timevalue(time(E2,F2,G2)))*1000),timevalue(time(I2,J2,0))*1000)/1000,"DD/MM/YYYY HH:MM:SS")&"*")

I would love the sequence to be generated the next day as well. This means if today is March 10 and start time is 10 with end time is 21 hrs

I need a sequence beyond the next day will resume from 11 March after 10 am

enter image description here

Those red dates must be skipped and continued from the next day

google-sheets-formula excel-formula
1 Answers

Basically, you wanted a sequence like this to generate times:

1
2
3
4
1
2
3
4
1
2
3
4

And sequence like this to generate days:

1
1
1
1
2
2
2
2
3
3
3
3
4
4
4
4

right?

If I'm correct, you should better change your sets to these:

enter image description here

You'll need these values:

10/03/2022  Start Date  [F9]
        
10:01:00    Start Time  [F11]
02:30:00    Step Time   [F12]
        
5   Number of times in a day    [F14]
60  Total number of Points is sequence  [F15]

And there are 2 formulas, to make it simple.

The formula for time & time sequence is:

=ArrayFormula(F11 +F12*MOD(SEQUENCE(F15)-1,F14)+1)

And the formula for dates & dates sequence is:

=ArrayFormula(F9 -1 + CEILING(SEQUENCE(F15)/F14))

https://docs.google.com/spreadsheets/d/1TzkwCAsAjM_yK7jCwEj_iZD2wTxHWmbtkyZROk4lCJU/copy

user_53724000
Related