Increase time by 15 minutes by 4 rows per 'time slot'

I'm trying to create a list of certain times for people to sign up in a form.

I did the following by hand, but I'd like to use a formula for it. Looked over the internet for iteration formulas for time but no success, so that's why I ask here.

This is my sheet now :

https://i.imgur.com/6U1WucC.png

As you can see there are 4 time slots for every time available (4 rows from 12:30, 4 rows from 12:45)

I have to extend these time slots till 18:00.

Someone knows how to do this by formula since it's really time consuming to do this by hand.

This should be the output:

06.01.2022 12:30

06.01.2022 12:30

06.01.2022 12:30

06.01.2022 12:30

06.01.2022 12:45

06.01.2022 12:45

06.01.2022 12:45

06.01.2022 12:45

06.01.2022 13:00

06.01.2022 13:00

06.01.2022 13:00

06.01.2022 13:00

And so on. Till 18:00.

Any help is highly appreciated!

Solution 1:

There are a few ways to do this. Here's one:

1. Input the first time in A1
2. Input this formula into A2: =IF(COUNTIF(A$1:A1,A1)<4,A1,A1+TIMEVALUE("0:15"))
3. Copy / paste the formula down

It will count how many of the cells above have the same time as the cell right above. By using the reference A$1:A1, the first row number will stay as A$1 and the second will update as it's copy / pasted down. For instance, the formula in A5 would be =IF(COUNTIF(A$1:A4,A4)<4,A4,A4+TIMEVALUE("0:15")). If there are already four instances of the above time, then it adds 15 minutes to it. Otherwise, it'll be the same value as the one above.

Of course, you'll have to update all the range references to match your actual data sheet.