I wish to compare a particular date in formula itself.

Prashant Kumar · 05/12/21

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!

Paul Newcome · 05/13/21

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")

Paul Newcome · 05/13/21

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")

Heather Duff · 05/13/21

@Paul Newcome @Prashant Kumar

I wonder if we're actually looking for a countifs:

=countifs([date1]:[date1], >DATE(2020,1,4),[date2]:[date2],"B")

Prashant Kumar · 05/13/21

https://community.smartsheet.com/discussion/comment/291151#Comment_291151

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!

I wish to compare a particular date in formula itself.

Best Answer

Answers

Help Article Resources

Categories