#N/A Error When Formula Connected to Date Formula

[marvin jones]

Hi All,

I'm getting an error in cell AU4 with the formula =LOOKUP(2,1/(Origami!$J$2:$J$227=AR4)/(Origami!$Z$2:$Z$227=AT4)/(Origami!$BN$2:$BN$227=AS4),(Origami!W$2:W$1000)). I can see this is connected to the date formula in cell AR4. If i hard code the date in cell AR4, the formula in cell AU4 works. I have the below;

AQ4 |has formula| |AR4 =AQ4| |AU4 = formula above|
05/01/2018 11:00 |05/01/2018 #N/A

How can i get the formula to work without hard coding the dates?

Thanks,

 

[Scott T]

The two dates are not the same.
05/01/2018 11:00 =43221.4583333333

05/01/2018 = 43221

Date and time in excel is a number. The date is the number of dates since 12/31/1899, 1/1/1900=1 1/2/1900=2 and so on. Time is part of the 24 hour day noon is .5 since it is half a day.

 

[marvin jones]

The two dates are not the same.
05/01/2018 11:00 =43221.4583333333

05/01/2018 = 43221

Date and time in excel is a number. The date is the number of dates since 12/31/1899, 1/1/1900=1 1/2/1900=2 and so on. Time is part of the 24 hour day noon is .5 since it is half a day.

Ah, OK, thanks Scott. So, how do i fix this so i can get the formula to work? There must be a way!

 

[Scott T]

If 5/1/18 with no time works then the INT function will remove the decimals.

=INT(your formula that returns the date here)

 

[marvin jones]

If 5/1/18 with no time works then the INT function will remove the decimals.

=INT(your formula that returns the date here)

Thanks Scott - that worked just fine.

 

[Scott T]

You are welcome