If-Then Date Type = Specific Date Issues

[Lauren]

Hi,

I have a column called [Date] with data type "Date". In my excel spreadsheet input, my dates look like "Aug-17" for August 2017. When I output the column it looks like "8/1/2017". All I want to do is write an If Statement so that if the date has 2099 in it as the year, it will become NULL(). Most of these dates are December of 2099. Here is what I have tried:

If [Date] = "12/1/2099" Then
        NULL()
Else
        [Date]
Endif

If [Date] = "Dec-2099" Then
        NULL()
Else
        [Date]
Endif

If [Date] = "2099-12-01" Then
        NULL()
Else
        [Date]
Endif

What form am I supposed to be putting and why won't any of these work?? My If Statement works, but it just runs through my IFs and nothing passes the test that [Date] = "12/1/2099".

Thanks to anyone who helps!

[Claje]

Hi,

If the december 1 2099 value is accurate, your third formula should work.

However, if you are having trouble with this, I suggest trying the following formula:

IF Datetimeformat([Date],'%Y')="2099" THEN
NULL()
ELSE
[Date]
ENDIF

This will let you just check the year and compare it to the string value 2099, which should accomplish what you want correctly.

[David92 · Accepted solution]

Hi Lauren

Give this a go and see if it works. 

Best 

DT

[Lauren]

Thank you!