Formatting Midnight in a date to 24 instead of 0:00

[shepcs]

Is there anyway by number formatting to change a database showing midnight at 0:00 the next day but rather as 24 the previous? I've messed around with some custom formatting I've seen but cant get it to work

Book1
	I
5	RAW DATA
6	Date/Time
7
8	1/1/2018 0:00
9	1/1/2018 1:00
10	1/1/2018 2:00
11	1/1/2018 3:00
12	1/1/2018 4:00
13	1/1/2018 5:00
14	1/1/2018 6:00
15	1/1/2018 7:00
16	1/1/2018 8:00
17	1/1/2018 9:00
18	1/1/2018 10:00
19	1/1/2018 11:00
20	1/1/2018 12:00
21	1/1/2018 13:00
22	1/1/2018 14:00
23	1/1/2018 15:00
24	1/1/2018 16:00
25	1/1/2018 17:00
26	1/1/2018 18:00
27	1/1/2018 19:00
28	1/1/2018 20:00
29	1/1/2018 21:00
30	1/1/2018 22:00
31	1/1/2018 23:00
32	1/2/2018 0:00
Sheet1

 

[awoohaw]

I got this to work for me. Using Conditional Formatting when the MOD of the Date Value is 1. It needs to be a date or a regular value as the TIME function resets to 0 at midnight.
THe conditional formatting for midnight is [hh]:mm and all other times hh:mm.

Mr excel questions 68.xlsm
	A	B
1
2
3	00:00	0.000000
4	01:00	0.041667
5	02:00	0.083333
6	03:00	0.125000
7	04:00	0.166667
8	05:00	0.208333
9	06:00	0.250000
10	07:00	0.291667
11	08:00	0.333333
12	09:00	0.375000
13	10:00	0.416667
14	11:00	0.458333
15	12:00	0.500000
16	13:00	0.541667
17	14:00	0.583333
18	15:00	0.625000
19	16:00	0.666667
20	17:00	0.708333
21	18:00	0.750000
22	19:00	0.791667
23	20:00	0.833333
24	21:00	0.875000
25	22:00	0.916667
26	23:00	0.958333
27	24:00	1.000000
28	01:00	0.041667
Sheet3

Cell Formulas
Range	Formula
A3	A3	=0+TODAY()-INT(TODAY())
B3:B28	B3	=MOD(A3,1)
A4:A28	A4	=A3+1/24

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:A28	Expression	=MOD(A3,1)<>1	text	NO
A3:A28	Expression	=MOD(A3,1)	text	NO

 

[shepcs]

Thank you! I"ll try it now.

 

[Tetra201]

Another option: display the desired date/time as text using the following formula:

=IF(MOD(ROUND(I8,5),1),TEXT(I8,"m/d/yyyy h:mm"),TEXT(I8-0.01,"m/d/yyyy")&" 24:00")

Note: these text strings can be used in calculations -- Excel will convert them into real date/time values.

 

[shepcs]

Tetra thank you! That worked a little easier. Very much appreciated!