How do I formulate date change at midnight?

[primedb]

I would like a formula in column A that recognizes midnight and advance the day. I only want this to happen at midnight as shown. The yellow cells are hard keyed. Column B is a simple formula =B3+1/24. I need to be able to change the date A3 and the time B3 and have it automatically update the rest of the sheet.

 

[Snakehips]

@primedb Welcome.

Maybe like..

Book1
	A	B
1
2	Date	Time
3	Sunday, June 5, 2022	14:00
4		15:00
5		16:00
6		17:00
7		18:00
8		19:00
9		20:00
10		21:00
11		22:00
12		23:00
13	Monday, June 6, 2022	00:00
14		01:00
15		02:00
16		03:00
17		04:00
18		05:00
19		06:00
20		07:00
Sheet1

Cell Formulas
Range	Formula
A4:A20	A4	=IF(B4=1,MAX(A$3:A3)+1,"")
B4:B20	B4	=B3+1/24

Hope that helps.

 

[primedb]

It works for one day. I need it to span 2 to 3 days. Thanks.

 

[jtakw]

It works for one day. I need it to span 2 to 3 days. Thanks.

Hi,

It should work for as many days as you drag the formula down.

 

[primedb]

Sunday, June 5, 2022	14:00​
	15:00​
	16:00​
	17:00​
	18:00​
	19:00​
	20:00​
	21:00​
	22:00​
	23:00​
Monday, June 6, 2022	0:00​
	1:00​
	2:00​
	3:00​
	4:00​
	5:00​
	6:00​
	7:00​
	8:00​
	9:00​
	10:00​
	11:00​
	12:00​
	13:00​
	14:00​
	15:00​
	16:00​
	17:00​
	18:00​
	19:00​
	20:00​
	21:00​
	22:00​
	23:00​
	0:00​

It is not capturing the second date change.

 

[jtakw]

Ok,

Try this:

Book3.xlsx
	A	B
2	Date	Time
3	6/5/2022	14:00
4		15:00
5		16:00
6		17:00
7		18:00
8		19:00
9		20:00
10		21:00
11		22:00
12		23:00
13	6/6/2022	0:00
14		1:00
15		2:00
16		3:00
17		4:00
18		5:00
19		6:00
20		7:00
21		8:00
22		9:00
23		10:00
24		11:00
25		12:00
26		14:00
27		15:00
28		16:00
29		17:00
30		18:00
31		19:00
32		20:00
33		21:00
34		22:00
35		23:00
36	6/7/2022	0:00
37		1:00
38		2:00
39		3:00
40		4:00
41		5:00
42		6:00
43		7:00
44		8:00
45		9:00
46		10:00
47		11:00
48		12:00
49		14:00
50		15:00
51		16:00
52		17:00
53		18:00
54		19:00
55		20:00
56		21:00
57		22:00
58		23:00
59	6/8/2022	0:00
60		1:00
Sheet1080

Cell Formulas
Range	Formula
B50:B60,B27:B48,B4:B25	B4	=B3+1/24
A4:A60	A4	=IF(MOD(ROUND(B4,2),1)=0,MAX(A$3:A3)+1,"")

 

[primedb]

Thanks this worked perfectly.