Help with multiple IF formula

Laura Byrne

Hi,

I am wondering if someone can help with a formula please.

I have two date columns which lookups to a cell and gives it a start date and an end date. Which is usually a start of the month and end of the month date.

I need a formula that looks at 'Date Completed' and if it is within the start date and end date then it say "Completed in Month" . If the 'Date Completed' is outside of the start date and end date then it is 'Not Completed in Month'. If the 'Date Completed' is blank then it says nothing.

Thank you.

Laura

Paul Newcome

Try something like this:

=IF([Date Completed]@row <> "", IF(AND([Date Completed]@row>= [Start Date]@row, [Date Completed]@row<= [End Date]@row), "Completed in Month", "Not Completed in Month"))

Laura Byrne

Hi Paul,

Thanks for your help, this is what your suggestion looks like with the correct column names in the smartsheet.

=IF([Date Certificate sent to Team or 3rd Party sent Ce]@row <> "", IF(AND([Date Certificate sent to Team or 3rd Party sent Ce]@row >= [Month Start]@row, [Date Certificate sent to Team or 3rd Party sent Ce]@row <= [Month End]@row), "Completed in Month", "Not Completed in Month"))

However it is saying #INVALIDOPERATION at the moment, do you know why this may be? Thanks.

Paul Newcome

Double check that all of the columns containing dates are set to date type columns and that all of those columns are storing actual dates instead of text strings that just look like dates.

Laura Byrne

Ah is it because my month start and month end date column is a formula that returns a date?

I need the month start and month end date to be based on another cell value so I have used a lookup for these cells and it returns a date in both cells.

Is there any way I can get round this? Thank you for your help.

Laura Byrne

It is working now thank you :)

Paul Newcome

Glad you were able to get it working. What was the issue?