Incriment time in hours between 8AM - 9PM

[freud1]

Hi Guys.

I have a date in cell A1

I need a formula to fill from A2 downwards that will fill the dates down adding 1 hour to each cell until it reaches 21:00 (9pm) then the next cell will start at 08:00 (8am) on the next day.

So it should look like this:

01/01/13 19:00
01/01/13 20:00
01/01/13 21:00
02/01/13 08:00
02/01/13 09:00

Any help would be much appreciated!

F

[Ace_XL]

Assuming A1 has your first entry, starting with A2 use

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

copy down

[Marcol]

I have a date in cell A1

If you only have a start date in A1 (No time)

Please Login or Register  to view this content.

Or this adaption of Ace_XLs' second suggestion

Please Login or Register  to view this content.

[newdoverman]

If the date and time are in A1 entered as you show with the format for column A set as dd/mm/yy h:mm enter this formula in A2 and copy down as far as you want:

=IF(HOUR(A1)<21,A1+1/24,A1+11/24)

Results as copied from my worksheet
01/01/13 8:00
01/01/13 9:00
01/01/13 10:00
01/01/13 11:00
01/01/13 12:00
01/01/13 13:00
01/01/13 14:00
01/01/13 15:00
01/01/13 16:00
01/01/13 17:00
01/01/13 18:00
01/01/13 19:00
01/01/13 20:00
01/01/13 21:00
02/01/13 8:00
02/01/13 9:00
02/01/13 10:00
02/01/13 11:00
02/01/13 12:00