Advance date after midnight

[l1011driver]

Thank you in advance to anyone who might have a suggestion.

I have a spreadsheet that has time in 1 minute increments. In an adjacent column I have a date corresponding to each 1-minute increment of time.

What I would like to do is have the date cell advance to the next day when the corresponding time cell reaches midnight (00:00). Subsequent date cells would reflect the "new" date until again reaching midnight on that day.

If you have any suggestions or help I'd be grateful. It seems my laptop doesn't like XL2BB so I'll attach a screen shot of what I'm talking about. I use a formula for time in column C, but column B is just the date copied to each subsequent cell. The formula bar shows the formula I use for the 1-minute increments of time.

Thank you for any help.

Sincerely,

L1011driver

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1
2
3
4			18/05/2023
5			23:50
6
7		18/05/2023	23:51
8		18/05/2023	23:52
9		18/05/2023	23:53
10		18/05/2023	23:54
11		18/05/2023	23:55
12		18/05/2023	23:56
13		18/05/2023	23:57
14		18/05/2023	23:58
15		18/05/2023	23:59
16		19/05/2023	00:00
17		19/05/2023	00:01
18		19/05/2023	00:02
19		19/05/2023	00:03
20		19/05/2023	00:04
21		19/05/2023	00:05
22
Sheet4

Cell Formulas
Range	Formula
B7:B21	B7	=C4+C5+TIME(0,SEQUENCE(15),0)
C7:C21	C7	=C5+TIME(0,SEQUENCE(15),0)
Dynamic array formulas.

 

[Joe4]

Try adding these formulas to cells B7 and C7 and copying down:

B7:

=IF(ROW()=7,$C$4,IF(INT(C7)=INT(C6),B6,B6+1))

C7:

=IF(ROW()=7,C5+(1/1440),C6+(1/1440))

 

[Fluff]

If you want col B to have just the date you could use

=INT(C4+C5+TIME(0,SEQUENCE(15),0))

 

[Georgiboy]

My effort:

Book1
	B	C
4	Start Date:	18/05/2023
5	Start Time:	23:50
6	Minutes to run:	20
7
8	Date	Time
9	18/05/2023	23:51:00
10	18/05/2023	23:52:00
11	18/05/2023	23:53:00
12	18/05/2023	23:54:00
13	18/05/2023	23:55:00
14	18/05/2023	23:56:00
15	18/05/2023	23:57:00
16	18/05/2023	23:58:00
17	18/05/2023	23:59:00
18	19/05/2023	00:00:00
19	19/05/2023	00:01:00
20	19/05/2023	00:02:00
21	19/05/2023	00:03:00
22	19/05/2023	00:04:00
23	19/05/2023	00:05:00
24	19/05/2023	00:06:00
25	19/05/2023	00:07:00
26	19/05/2023	00:08:00
27	19/05/2023	00:09:00
28	19/05/2023	00:10:00
Sheet1

Cell Formulas
Range	Formula
B9:C28	B9	=LET( t,C5+TIME(0,SEQUENCE(C6),0), d,SCAN(C4,t,LAMBDA(a,x,IF(x=1,a+1,a))), HSTACK(d,t))
Dynamic array formulas.

 

[l1011driver]

How about

Fluff.xlsm
	A	B	C
1
2
3
4			18/05/2023
5			23:50
6
7		18/05/2023	23:51
8		18/05/2023	23:52
9		18/05/2023	23:53
10		18/05/2023	23:54
11		18/05/2023	23:55
12		18/05/2023	23:56
13		18/05/2023	23:57
14		18/05/2023	23:58
15		18/05/2023	23:59
16		19/05/2023	00:00
17		19/05/2023	00:01
18		19/05/2023	00:02
19		19/05/2023	00:03
20		19/05/2023	00:04
21		19/05/2023	00:05
22
Sheet4

Cell Formulas
Range	Formula
B7:B21	B7	=C4+C5+TIME(0,SEQUENCE(15),0)
C7:C21	C7	=C5+TIME(0,SEQUENCE(15),0)
Dynamic array formulas.

When I do this I get an error that says "#SPILL!".

 

[Fluff]

In that case you need to clear all the cells below the formula.

 

[l1011driver]

Try adding these formulas to cells B7 and C7 and copying down:

B7:

=IF(ROW()=7,$C$4,IF(INT(C7)=INT(C6),B6,B6+1))

C7:

=IF(ROW()=7,C5+(1/1440),C6+(1/1440))

Similar to a previous suggestion, I get an error that says, "#SPILL!".

 

[l1011driver]

In that case you need to clear all the cells below the formula.

OK, let me try...

 

[Joe4]

Similar to a previous suggestion, I get an error that says, "#SPILL!".

My formula does not create a spill situation.
Sounds like you have other existing formulas on your sheet interfering with it.