List consecutive shifts that occur within timeframe

Hi!

1. I have a timeframe within "bookend 1" and "bookend 2". These bookends may start and stop at various times throughout the week and the time range between them can be of various length. "Bookend 1" will always be earlier than "bookend 2".
2. Throughout the day, there are predefined times when "shift handovers" occur. These times are 07:00, 14:00 and 23:00 around the clock.
3. While the bookends are entered manually into cell A1 and A6, I need formulas in cells A2 to A5 that shows the "shift handover" times consecutively. In the example below, "bookend 1" starts at 22:00, which means that the first possible "shift handover" will happen one hour later at 23:00. The formula must then enter date and time (Wednesday 4 May 2016 23:00 in any given format) in cell A2. In cell A3, I need the next upcoming shift and so forth until cell A5 is completed with a "shift handover" date/time or "bookend 2" limits the number of "shift handovers" that fit in the time range. (If the "bookend" range is very narrow, one or more of the cells A2 to A5 may be "blank" as there won't be enough "shift handovers" to fit within the range to fill out all the cells).

Does anybody have a suggestion for an efficient formula to achieve this?

Cell A1: Wednesday 4 May 2016 22:00 ("Bookend 1")
Cell A2: Wednesday 4 May 2016 23:00 ("Shift handover at appointed time")
Cell A3: Thursday 5 May 2016 07:00 ("Shift handover at appointed time")
Cell A4: Thursday 5 May 2016 14:00 ("Shift handover at appointed time")
Cell A5: Thursday 5 May 2016 23:00 ("Shift handover at appointed time")
Cell A6: Friday 6 May 2016 00:00 ("Bookend 2")

Best regards,
Marbleking

Hi
Try this
In D1:D4 put the values =7/24 , =14/24 , =23/24, =D1+1 (={0,291666666666667;0,583333333333333;0,958333333333333;1,29166666666667})
In A1 put your date-time 4-05-2016 22:00
Use in A2 the following

Formula:

Please Login or Register  to view this content.

and copy down

Hi, José Augusto

Thanks for the formula; it works! :-)

I had to start the value list in D1:D5 with =-1/24 to get it to also cover starting times 00:00 - 06:00.

Best regards,
Øystein

Hi Øystein
I'm glad to have helped.
Do not forget to mark the thread as SOLVED