Missing date formula syntax

[normpam]

If A2 has a date like 11/30/16, and E2 has 03/31/17, then a formula: =A2>E2 returns false. But, if if I type =A2>03/31/17, it returns 'True', which is not correct. I had the same issue using a date within a conditional formatting formula. Isn't there a syntax to use besides using the =Date() syntax?

Thanks!

 

[Tetra201]

Try

=A2>"03/31/17"+0

 

[JoeMo]

If you want to use a formula, you can enter 3/31/17 in say A1 and the formula becomes: =A2>A1, which returns FALSE or you can use the serial number for 3/31/17 which is 42825: =A2>42825 if you don't want to use the DATE function.

 

[normpam]

Thanks Tetra... I thought I saw once a long time ago that dates should be surrounded with # signs, but I also tried quotation marks, but it doesn't work without your extra +0. Why does the +0 make it work??

 

[Tetra201]

Dates in Excel are numbers.
"03/31/17" is a text string.
The +0 converts the text string into a number.

 

[normpam]

Dates in Excel are numbers.
"03/31/17" is a text string.
The +0 converts the text string into a number.

Thanks! Just got back to this one today. Two more ideas.... First, I found that multiplying by 1 also works. Also, I could almost swear that years ago I was able to work with dates within a formula by using the # sign before and after the date.... was there every another syntax in Excel that worked?

 

[Joe4]

I could almost swear that years ago I was able to work with dates within a formula by using the # sign before and after the date.... was there every another syntax in Excel that worked?

That is a common way of referencing dates in Access queries...

 

[JoeMo]

I could almost swear that years ago I was able to work with dates within a formula by using the # sign before and after the date.... was there every another syntax in Excel that worked?

#mm/dd/yyyy# is a way of entering hard-coded dates in VBA.

Dim Dte as Date
Dte = #10/28/2017#