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

 

[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))

When I try to drag the formula it gives me a circular reference warning.

It's getting pretty late on my side of the world. I'm going to regroup and try again tomorrow.

Thank you so much for your help and patience. I apologize for my lack of proficiency.

 

[l1011driver]

OK, let me try...

It's getting late on my side of the world. I'll try again tomorrow.

Thank you for your help and patience. I apologize for my lack of proficiency.

 

[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.

HI George,

It's getting late here on my side of the world. I'll work on it more tomorrow.

Thank you so much for your help and patience.

Take care and stay safe.

Yours,

Mark

 

[Joe4]

When I try to drag the formula it gives me a circular reference warning.

It's getting pretty late on my side of the world. I'm going to regroup and try again tomorrow.

Thank you so much for your help and patience. I apologize for my lack of proficiency.

Sounds like you may have put the formulas in the wrong cells.
Note that the way I wrote them is for cells B7 and C7.
If you are putting them in any other cell to start, you may need to adjust the formula accordingly.

 

[l1011driver]

I'm thinking you are absolutely correct. I'm positive the problem is in my incorrect entry of your formulas. As such I'm attaching an image of a portion of the actual spreadsheet I'm using. I hope this helps. We can put your cell references in their proper place and it should work fine. The example you gave me represents exactly what I want to do.

My wife and I are on the road today, but we'll arrive at our destination this evening local Thailand time. I'll be available after that for several days.

Thank you again for your help.

Sincerely,

Mark

 

[Fluff]

With that layout, the formula I suggested would be

Fluff.xlsm
	A	B	C	D	E
1
2			1	21/12/2023	23:56:00
3			2	21/12/2023	23:57:00
4			3	21/12/2023	23:58:00
5			4	21/12/2023	23:59:00
6			5	22/12/2023	00:00:00
7		21/12/2023	6	22/12/2023	00:01:00
8		23:55	7	22/12/2023	00:02:00
9			8	22/12/2023	00:03:00
10			9	22/12/2023	00:04:00
11			10	22/12/2023	00:05:00
12			11	22/12/2023	00:06:00
13
Sheet4

Cell Formulas
Range	Formula
C2:C12	C2	=SEQUENCE(11)
D2:D12	D2	=INT(B7+B8+TIME(0,SEQUENCE(11),0))
E2:E12	E2	=B8+TIME(0,SEQUENCE(11),0)
Dynamic array formulas.

 

[l1011driver]

Hi Fluff,

Your suggestion absolutely did the trick. It's working exactly like I wanted it to!

I wouldn't have come up with your Excel functions in a million years. Same can be said for Georgiboy and Joe4's solutions as well. I will research exactly how the functions you provided work. Just when I think I'm starting to get pretty good at Excel I realize how very little of it I actually know.

Many thanks to you, Georgiboy and Joe4 for all your help! I sincerely appreciate it!

Take care and stay safe.

Yours,

Mark

 

[Peter_SSs]

Hi Fluff,

Your suggestion absolutely did the trick. It's working exactly like I wanted it to!

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

 

[Fluff]

Glad we could help & thanks for the feedback.