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

 

[Georgiboy]

Have a look at the below link, look at the bit where it states: spill range isn't blank:
How to correct a #SPILL! error - Microsoft Support

 

[l1011driver]

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

I have to be doing something wrong. When I clear all the cells below the formula the first 15 or so cells remain and and I can neither clear the cell nor delete the cells.

If I drag the formula below those 15 or so cells I get the spill message again.

Sorry I'm not more proficient at this. I apologize.

 

[Fluff]

If I drag the formula below those 15 or so cells I get the spill message again.

Do not drag the formula down.

When I clear all the cells below the formula the first 15 or so cells remain

That's because the formula is spilling down 15 cells, you can change how many rows it spills by changing the 15 inside the sequence function.

 

[l1011driver]

Do not drag the formula down.

That's because the formula is spilling down 15 cells, you can change how many rows it spills by changing the 15 inside the sequence function.

Light bulb just came on.

Ok, I put the correct number of cells in. No SPILL error. I did not drag the formula. The date column cells filled up just fine, but when the date reaches the time of "00:00:00" in the adjacent cell the date does not advance.

 

[Joe4]

The formulas I posted do not cause spill situations.
You would just copy paste the formulas from B7:C7 down as far you want/need.

 

[Fluff]

I put the correct number of cells in

How many is that?

 

[l1011driver]

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.

Hey George,

Your example works exactly like I'm wanting it to, but when I plug your formula into my spreadsheet I get the SPILL message again. I clear all the cells below it, but the SPILL message appears in the cell in which I enter your formula.

 

[l1011driver]

The formulas I posted do not cause spill situations.
You would just copy paste the formulas from B7:C7 down as far you want/need.

Ok, I'll try again!

Thank you!

 

[l1011driver]

How many is that?

525

 

[Fluff]

It works for me with that number of rows.

Fluff.xlsm
	A	B	C
1
2
3
4			18/05/2023
5			23:50
6
7		18/05/2023	23:51:00
8		18/05/2023	23:52:00
9		18/05/2023	23:53:00
10		18/05/2023	23:54:00
11		18/05/2023	23:55:00
12		18/05/2023	23:56:00
13		18/05/2023	23:57:00
14		18/05/2023	23:58:00
15		18/05/2023	23:59:00
16		19/05/2023	00:00:00
17		19/05/2023	00:01:00
530		19/05/2023	08:34:00
531		19/05/2023	08:35:00
Sheet4

Cell Formulas
Range	Formula
B7:B531	B7	=INT(C4+C5+TIME(0,SEQUENCE(525),0))
C7:C531	C7	=C5+TIME(0,SEQUENCE(525),0)
Dynamic array formulas.