Return value if one condition is met

Veronica J

Good Morning,

I am trying this formula within my sheet in a brand new column:

=IF([Planned Start Date]@row - 21 <= TODAY(), [Estimated Hours Number]@row, "")

For this column, if someone put a date that falls between today and three weeks from today, to return the job site hours that is found in the column Estimated Hours Number. IF the date doesn't fall within the three weeks or if the cell is blank, return a blank. 

This formula keeps returning a value. The IF condition isn't met, it still returns the value found in column Estimated Hours Number. I don't understand how it is doing this when the condition is false. I have tried using "" and using 0. Do I need to restructure this formula?

Thank you,

Veronica

Veronica J

Using this:

=IF(AND([Planned Start Date]@row - 21 < TODAY(), [Planned Start Date]@row <> 0), [Estimated Hours Number]@row, "")

Formula helped to achieve a blank return because the planned start date was blank however, if the date falls into the three week period or has a date but it isn't in the three weeks, it returns error message "INVALID OPERATIONS"

Paul Newcome

Try something like this...

 

=IF([Planned Start Date]@row >= TODAY(-21), [Estimated Hours Number]@row)

Veronica J

Paul - the formula is pulling data outside of the range given. Essentially, if any date is listed in Planned Start Date column, it returns the hours. I just want it for today and three weeks from today.

Paul Newcome

3 weeks prior to or 3 weeks after?

 

3 Weeks after would be

 

=IF(AND([Planned Start Date]@row <= TODAY(21), [Planned Start Date]@row >= TODAY()), [Estimated Hours Number]@row)

Veronica J

Thank you Paul - it works! I needed anything scheduled for today + three weeks future. This is going to help with my dashboards to track scheduled work. 

-Veronica

Paul Newcome

Happy to help.

 

Sorry about the initial confusion.