Comparing dates in 2 different sheets columns

Manpreet Singh Manchanda

Hello, I am trying to compare dates (like < or >) between 2 different sheets columns. Can someone guide me using IF statement to do this?

Kelly Moore

Hey @Manpreet Singh Manchanda

When making comparisons on multiple sheets (smartsheet calls these cross-sheets), one cannot use simple IF statements. We have to use specific formulas such as COUNTIFS or SUMIFS to evaluate the data.

COUNTIFS can be used to verify that the IF condition is true. If the COUNTIFS returns a value greater than zero, it means there was something there to count. A zero means nothing was there.

=IF(COUNTIFS({the date field in your other sheet}, @cell < [date field of the sheet you're entering this formula in]@row) > 0, "whatever should happen when true", "whatever should happen when false"). If you are hard coding a specific date into the formula, use the format DATE(YYYY,MM,DD).

=IF(COUNTIFS({the date field in your other sheet}, @cell < DATE(YYYY,MM,DD)) > 0, "whatever should happen when true", "whatever should happen when false")

Since you are building a cross sheet reference, you cannot simply copy paste the formula above into your sheet. You must click the INSERT REFERENCE link in your formula window and highlight the column in the other sheet that you wish to insert. Remember that a good practice is to re-name that column from the generic smartsheet range number to the actual name of your column. This will help you and the community better understand the cross sheet formulas you are building.

If you provide screenshots and/or more detail then the community can help you build the specific formula you need.

cheers

Kelly Moore

Hey @Manpreet Singh Manchanda

When making comparisons on multiple sheets (smartsheet calls these cross-sheets), one cannot use simple IF statements. We have to use specific formulas such as COUNTIFS or SUMIFS to evaluate the data.

COUNTIFS can be used to verify that the IF condition is true. If the COUNTIFS returns a value greater than zero, it means there was something there to count. A zero means nothing was there.

=IF(COUNTIFS({the date field in your other sheet}, @cell < [date field of the sheet you're entering this formula in]@row) > 0, "whatever should happen when true", "whatever should happen when false"). If you are hard coding a specific date into the formula, use the format DATE(YYYY,MM,DD).

=IF(COUNTIFS({the date field in your other sheet}, @cell < DATE(YYYY,MM,DD)) > 0, "whatever should happen when true", "whatever should happen when false")

Since you are building a cross sheet reference, you cannot simply copy paste the formula above into your sheet. You must click the INSERT REFERENCE link in your formula window and highlight the column in the other sheet that you wish to insert. Remember that a good practice is to re-name that column from the generic smartsheet range number to the actual name of your column. This will help you and the community better understand the cross sheet formulas you are building.

If you provide screenshots and/or more detail then the community can help you build the specific formula you need.

cheers

Manpreet Singh Manchanda

Thank you KDM