Time and date sequencing together.

[Avalanchez]

Hello,

I am trying to figure out the best way to create a time and date sequence that work together. I can get the time to work correctly with the sequence formula but Im not sure how to incorporate the date. Im trying to have one cell have date (B1) and the next the time (C1), when the rest of the rows below those see that date and time I want a 6 hour sequence to follow for the next 13 rows. Can this be done by formula or would VBA be easier?
EX:

Start Times	2/20/2023	12:00
6 hr	2/20/2023	18:00
12 hr	2/21/2023	00:00
18 hr	2/21/2023	06:00
24 hr
30 hr
36 hr
42 hr
48 hr
54 hr
60 hr
66 hr
72 hr
End

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1	Start Times	20/02/2023	12:00:00
2		20/02/2023	18:00:00
3		21/02/2023	00:00:00
4		21/02/2023	06:00:00
5		21/02/2023	12:00:00
6		21/02/2023	18:00:00
7		22/02/2023	00:00:00
8		22/02/2023	06:00:00
9		22/02/2023	12:00:00
10		22/02/2023	18:00:00
11		23/02/2023	00:00:00
12		23/02/2023	06:00:00
13		23/02/2023	12:00:00
14		23/02/2023	18:00:00
15
Summary

Cell Formulas
Range	Formula
B2:C14	B2	=LET(a,SEQUENCE(13,,B1+C1+TIME(6,0,0),TIME(6,0,0)),HSTACK(INT(a),MOD(a,1)))
Dynamic array formulas.

 

[Avalanchez]

How about

Fluff.xlsm
	A	B	C
1	Start Times	20/02/2023	12:00:00
2		20/02/2023	18:00:00
3		21/02/2023	00:00:00
4		21/02/2023	06:00:00
5		21/02/2023	12:00:00
6		21/02/2023	18:00:00
7		22/02/2023	00:00:00
8		22/02/2023	06:00:00
9		22/02/2023	12:00:00
10		22/02/2023	18:00:00
11		23/02/2023	00:00:00
12		23/02/2023	06:00:00
13		23/02/2023	12:00:00
14		23/02/2023	18:00:00
15
Summary

Cell Formulas
Range	Formula
B2:C14	B2	=LET(a,SEQUENCE(13,,B1+C1+TIME(6,0,0),TIME(6,0,0)),HSTACK(INT(a),MOD(a,1)))
Dynamic array formulas.

I think that'll work thank you very much.

 

[Avalanchez]

How about

Fluff.xlsm
	A	B	C
1	Start Times	20/02/2023	12:00:00
2		20/02/2023	18:00:00
3		21/02/2023	00:00:00
4		21/02/2023	06:00:00
5		21/02/2023	12:00:00
6		21/02/2023	18:00:00
7		22/02/2023	00:00:00
8		22/02/2023	06:00:00
9		22/02/2023	12:00:00
10		22/02/2023	18:00:00
11		23/02/2023	00:00:00
12		23/02/2023	06:00:00
13		23/02/2023	12:00:00
14		23/02/2023	18:00:00
15
Summary

Cell Formulas
Range	Formula
B2:C14	B2	=LET(a,SEQUENCE(13,,B1+C1+TIME(6,0,0),TIME(6,0,0)),HSTACK(INT(a),MOD(a,1)))
Dynamic array formulas.

One last thing, If those top two cells are blank, can it keep the rest of the cells blank until entered?

 

[Fluff]

How about

=LET(a,SEQUENCE(13,,B1+C1+TIME(6,0,0),TIME(6,0,0)),IF(OR(B1="",C1=""),"",HSTACK(INT(a),MOD(a,1))))

 

[Avalanchez]

How about

=LET(a,SEQUENCE(13,,B1+C1+TIME(6,0,0),TIME(6,0,0)),IF(OR(B1="",C1=""),"",HSTACK(INT(a),MOD(a,1))))

How would This code be written for older versions of excel without the LET function?

 

[Fluff]

What older versions do you mean?

 

[Avalanchez]

What older versions do you mean?

Well my work book was built on 365 but i have an old laptop with excel 2013 that the LET function isnt implemented in. I kind of tried chopping it up a little myself but just cant get it quite right.

 

[Fluff]

Ok, how about

Fluff.xlsm
	A	B	C
1	Start Times	20/02/2023	12:00:00
2		20/02/2023	18:00:00
3		21/02/2023	00:00:00
4		21/02/2023	06:00:00
5		21/02/2023	12:00:00
6		21/02/2023	18:00:00
7		22/02/2023	00:00:00
8		22/02/2023	06:00:00
9		22/02/2023	12:00:00
10		22/02/2023	18:00:00
11		23/02/2023	00:00:00
12		23/02/2023	06:00:00
13		23/02/2023	12:00:00
14		23/02/2023	18:00:00
Main

Cell Formulas
Range	Formula
B2:B14	B2	=INT(B1+C1+TIME(6,0,0))
C2:C14	C2	=MOD(B1+C1+TIME(6,0,0),1)

 

[Avalanchez]

Ok, how about

Fluff.xlsm
	A	B	C
1	Start Times	20/02/2023	12:00:00
2		20/02/2023	18:00:00
3		21/02/2023	00:00:00
4		21/02/2023	06:00:00
5		21/02/2023	12:00:00
6		21/02/2023	18:00:00
7		22/02/2023	00:00:00
8		22/02/2023	06:00:00
9		22/02/2023	12:00:00
10		22/02/2023	18:00:00
11		23/02/2023	00:00:00
12		23/02/2023	06:00:00
13		23/02/2023	12:00:00
14		23/02/2023	18:00:00
Main

Cell Formulas
Range	Formula
B2:B14	B2	=INT(B1+C1+TIME(6,0,0))
C2:C14	C2	=MOD(B1+C1+TIME(6,0,0),1)

That was enough to get me over the hump! I appreciate it AGAIN! Thanks