If statement stopped working from one cell to the next, with formatting all the same

[Guzzlr]

Hello, I have a simple if statement that works down to row 51, bur for some reason, it just stops working, and all formatting and formulas remain the same.
Notice in P51 has the exact same formula as P6:P50 and it is working. However, in P51, and further down, it stops working.
I dont know why. Any comments on this oddity?
Thank you

3-21-2024_PM-Scheduling Spike Template_Orig.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P
1													50
2													50
3												4/1/2024	4/1/24
4
5	Planned Start Date	Asset Name	Facility Number	Responsible Org (Shop)	Resource Assignment Status	Work Task Description	Asset ID	Asset Description	Est PM Duration	Total Planned Working Hours	Total Planned Working Hours Corrected
6	04/01/24										0.60		0.6
7	04/01/24										0.87		0.9
8	04/01/24										1.12		1.1
9	04/01/24										0.54		0.5
10	04/01/24										0.54		0.5
11	04/01/24										0.54		0.5
12	04/01/24										0.54		0.5
13	04/01/24										1.20		1.2
14	04/01/24										1.20		1.2
15	04/01/24										1.20		1.2
16	04/01/24										1.20		1.2
17	04/01/24										1.20		1.2
18	04/01/24										0.97		1.0
19	04/01/24										1.31		1.3
20	04/01/24										1.31		1.3
21	04/01/24										1.31		1.3
22	04/01/24										1.31		1.3
23	04/01/24										0.86		0.9
24	04/01/24										0.86		0.9
25	04/01/24										0.86		0.9
26	04/01/24										0.86		0.9
27	04/01/24										1.50		1.5
28	04/01/24										1.50		1.5
29	04/01/24										1.20		1.2
30	04/01/24										1.71		1.7
31	04/01/24										1.71		1.7
32	04/01/24										1.02		1.0
33	04/01/24										1.04		1.0
34	04/01/24										4.93		4.9
35	04/01/24										1.50		1.5
36	04/01/24										2.92		2.9
37	04/01/24										0.64		0.6
38	04/01/24										0.64		0.6
39	04/01/24										1.50		1.5
40	04/01/24										0.63		0.6
41	04/01/24										0.63		0.6
42	04/01/24										0.54		0.5
43	04/01/24										0.60		0.6
44	04/01/24										0.60		0.6
45	04/01/24										1.00		1.0
46	04/01/24										0.54		0.5
47	04/01/24										1.00		1.0
48	04/01/24										0.59		0.6
49	04/01/24										1.28		1.3
50	04/01/24										0.30		0.3
51	4/1/2024										1.02
52	04/01/24										1.20
53	04/01/24										1.20
54	04/01/24										1.20
55	04/01/24										0.60
56	04/01/24										0.60
57	04/01/24										1.20
Data

Cell Formulas
Range	Formula
O3	O3	=D6
P1	P1	=SUM(P4:P2500)
P2	P2	=SUBTOTAL(9,P4:P2500)
P3	P3	=O3
P4:P5	P4	=IF(AND(P$3>=$B4,P$3<=$C4),$L4,"")
P6:P49	P6	=IF(P$3=D6,N6,"4")
P50:P57	P50	=IF(P$3=D50,N50,"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N1:HAG1	Expression	=N$1>=250	text	NO

 

[StephenCrump]

No mystery. Your dates aren't integer, they're actually date plus time.

There is a small difference between:
P3 = 45383.29
D51 = 45383.31

 

[Guzzlr]

oh...you are correct: This is how the format for Start Date is coming from the report I run: 04/01/2024 07:00:00

Below is the VBA I use to get rid of the time stamp to only show date:

Columns("D:D").NumberFormat = "m/d/yyyy"

Do you have the time to suggest code I should use, to leave only the date in m/d/yyyy format, with out the time stamp?
Thank you for your help

 

[Joe4]

Below is the VBA I use to get rid of the time stamp to only show date:

That will only affect the formatting/view, not the underlying value!

In an Excel formula, you can use the INT or TRUNC functions to remove the decimals off of numbers.
In VBA, if you using Variables, just declare your variables to be of the INTEGER or LONG types.

 

[Guzzlr]

In VBA, if you using Variables, just declare your variables to be of the INTEGER or LONG types.

So I tried this, but it faults on me. So it's obviously not what you mean...sorry.

Dim d As Long
d = Columns("D:D").NumberFormat = "mm/dd/yy"

 

[StephenCrump]

To convert using VBA, you could do something like this:

With Range("D6:D" & Range("D" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate("=IF(" & .Address & "="""","""",INT(" & .Address & "))")
End With

There are other options, as @Joe4 says.

For example, you could simply change:

O3 to = INT(D6), and
P6 to =IF(P$3=INT(D6),N6,"4") (It's not clear why you're using text "4" rather than numeric 4, and why this changes to "" further down the column?)

Or you could use a helper column to calculate integer values, e.g. Q6=INT(D6) copied down, and then copy/paste values back over column D.

 

[Joe4]

So I tried this, but it faults on me. So it's obviously not what you mean...sorry.

Dim d As Long
d = Columns("D:D").NumberFormat = "mm/dd/yy"

You are mixing up two very different things!
Changing the formatting of a column is very different than truncating the decimals values off of numbers.
(And you cannot set a WHOLE column equal to a single numeric variable!)

Steven shows you the details of various different ways you can do it.
Of course, if you are trying to incorporate this into some pre-existing VBA code that you have, it would be most helpful to post that code.

 

[Guzzlr]

To convert using VBA, you could do something like this:

With Range("D6:D" & Range("D" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate("=IF(" & .Address & "="""","""",INT(" & .Address & "))")
End With

There are other options, as @Joe4 says.

For example, you could simply change:

O3 to = INT(D6), and
P6 to =IF(P$3=INT(D6),N6,"4") (It's not clear why you're using text "4" rather than numeric 4, and why this changes to "" further down the column?)

Or you could use a helper column to calculate integer values, e.g. Q6=INT(D6) copied down, and then copy/paste values back over column D.

This is working nicely. Thank you very much for the help...