I wish to compare a particular date in formula itself.
For eg  =IF($[Date1]@row > "1/4/2020", COUNT($[Date2]@row, "B")
Right now my approach is updating the formula column properties as Date and updating 1st row as "1/4/2020" then comparing this with Date1. Issue with this is that the rest of the values in this column should be date or any string only. I want to add numeric values.
My approach eg  IF($[Date1]@row > [Apr 2019]$1, COUNT($[Date2]@row, "B")
I am getting error as #DATE EXPECTED.
Thanks in advance!
Best Answer

You should be putting the formula in a text/number column.
Additionally, if you are referencing a date in a formula, then you need to use the DATE function:
=IF($[Date1]@row > DATE(2020, 01, 04)
I am not really sure what you are trying to accomplish with the bold portion below though.
=IF($[Date1]@row > "1/4/2020", COUNT($[Date2]@row, "B")
Answers

You should be putting the formula in a text/number column.
Additionally, if you are referencing a date in a formula, then you need to use the DATE function:
=IF($[Date1]@row > DATE(2020, 01, 04)
I am not really sure what you are trying to accomplish with the bold portion below though.
=IF($[Date1]@row > "1/4/2020", COUNT($[Date2]@row, "B")

I wonder if we're actually looking for a countifs:
=countifs([date1]:[date1], >DATE(2020,1,4),[date2]:[date2],"B")

Hi Paul, Thanks for the solution. I figured out that we can use the DATE function like that.
COUNT function is just for an example, basically I wanted to have a calculated value as output in IF condition.
It really helps. Thanks!
Help Article Resources
Categories
Check out the Formula Handbook template!