0

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!

Improve this question
2
  • =A1+1/96? (assuming A1 has the first time
    – cybernetic.nomad
    Commented Dec 22, 2021 at 20:58
  • What do you mean? I've tried that and it gives me the #VALUE as a result
    – Mand
    Commented Dec 22, 2021 at 21:00

1 Answer 1

Reset to default
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.

Improve this answer
5
  • Thanks for the reply! Really appreciate it. But how should my A1 cell be formatted. I've now formatted it as time. Entered 12:15:00 AM. How do I add a formula on top of that?
    – Mand
    Commented Dec 22, 2021 at 21:20
  • @Mand A1 should be a value with whatever format you want. A2 should be the formula (again with whatever format you want). Then, you copy A2 and paste it down as far as you need.
    – Engineer Toast
    Commented Dec 22, 2021 at 21:30
  • I know but my excel was in a different language than usual so I had to adjust a bit. Did the trick though for me!
    – Mand
    Commented Dec 22, 2021 at 21:43
  • Ah, yes. Comma vs semicolon is a common source of trouble between languages.
    – Engineer Toast
    Commented Dec 22, 2021 at 21:51
  • 1
    Value in A1, formula in A2(!) seems more logical ;.) [nothing else will work]
    – Hannu
    Commented Dec 23, 2021 at 15:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .