IF-formula on a date range

Jordi de Jong

Hi guys,

For the last couple of days I've been trying to create a simple formula, but in some way it doesn't work.

I want to check if the date of today falls inbetween a specific date range (2 different columns), using the IF-formula.

I've tried this formulas:

=IF(AND([Date 1]1>=TODAY(),[Date 2]1<=TODAY), "True", "False")

=IF(AND([Date 1]1>TODAY(),[Date 2]1<TODAY), "True", "False")

But it doesn't appear to be working.

Would be nice to hear from you,

Jordi

smartsheet issue.JPG

Paul Newcome

Switch your arguments so that [Date 1] is less than TODAY and [Date 2] is greater than TODAY.

RossL

@Jordi de Jong

Try,

=IF(AND([Date 1]@row >= TODAY(), [Date 2]@row <= TODAY()), "True", "False")

Paul Newcome

The issue with your first formula is that you forgot the parenthesis for the second TODAY function.

You have:

=IF(AND(...................TODAY), ..........)

But it should be:

=IF(AND(...................TODAY()), ..........)

@RossL's solution has this corrected.

Jordi de Jong

I've tried the formula that @RossL made, with the fixed parenthesis, but the formula appears to be unparseable...

Paul Newcome

Can you provide a screenshot of the formula you are using within the sheet along with the referenced columns similar to the screenshot below?

Jordi de Jong

@Paul Newcome

Here you go:

Paul Newcome

Try replacing True and False with the numbers 1 and 2. Does that make any difference?

Jordi de Jong

Yes, that works

edit: it returns the value 2

Paul Newcome

Smartsheet doesn't seem to like using "True" and "False" values in formulas like that. I believe there was an instance where someone had a True column and a False column and it was throwing things off as well. It looks like you are going to want to adjust your output.

Jordi de Jong

But now it returns the value 2, but that is not the correct one, isn't it?

Because TODAY is between those dates, so it should return 1?

Paul Newcome

Switch your arguments so that [Date 1] is less than TODAY and [Date 2] is greater than TODAY.