Repeat a series of times

[randall78]

Hi,

How can i use a formula to repeat a sequence of time from 08:00 to 17:00. The result should look like below.

08:00 09:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 8:00 9:00 and so on.

I have tried using =MOD but with no luck.

[AliGW]

Which version of Excel are you using?

[AliGW]

If you have 365:

=SEQUENCE((A2*24)-(A1*24)+1,,A1,1/24)

Formatting for column C will be TIME.

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C
1	08:00		08:00:00
2	17:00		09:00:00
3			10:00:00
4			11:00:00
5			12:00:00
6			13:00:00
7			14:00:00
8			15:00:00
9			16:00:00
10			17:00:00

Sheet: Sheet1

[Fluff13]

How about

=TIME(MOD(COLUMNS($A1:A1)-1,10)+8,0,0)

dragged across

[AliGW]

Non-365

In E1:

=A1

In E2 copied down:

=IFERROR(IF(E1+1/24>$A$2,"",E1+1/24),"")

[JEC.]

Office 365:

=(MOD(SEQUENCE(200,,0),10)+8)/24

[randall78]

Thank you for the help.

All sorted.

[AliGW]

Glad to have helped.

If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[Fluff13]

Glad to help & thanks for the feedback.