Formula comparing dates

Dominique

I am very new to Smartsheet and the making of formula's overall so sorry for this question.

I want to create a column where it will be highlighted when the date from column x is 4days or less apart from the date from column y. Does anybody know which formula to use?

I started with this: =IF([column x]row number <

Many thanks in advance!

Stefan

Hi Dominique,

using the if formula is the right direction.

To achieve what you want I suggest using a helper column and a conditional format.

Add a column (you may hide it later) and name it for example "4orless". In the cells of this column you will place your formula. Example (my columns are named "start" and "end"): =IF(end@row - start@row < 4; "1"; "0").

Now create a conditional format that looks for rows where the column "4orless" has a 1 and if yes applies your format to the cell in column "end".

hope this helps

Stefan

Stefan

Hi Dominique,

using the if formula is the right direction.

To achieve what you want I suggest using a helper column and a conditional format.

Add a column (you may hide it later) and name it for example "4orless". In the cells of this column you will place your formula. Example (my columns are named "start" and "end"): =IF(end@row - start@row < 4; "1"; "0").

Now create a conditional format that looks for rows where the column "4orless" has a 1 and if yes applies your format to the cell in column "end".

hope this helps

Stefan

Dominique

Hi Stefan,

Thank you for your answer.

I created a column 4orless and placed the formula:

=IF(X@row - Y@row < 4; "1"; "0")

All the cells in that column now say: #UNPARSEABLE

Do I have to change anything?

Stefan

Hi Dominique,

are you working with the english version of Smartsheet?

If yes, you need to use , instead of ;

Hope this helps

Stefan

Dominique

I changed the ; to , but still it is still the same.

Stefan

Hi Dominique,

are the columns "X" and "Y" configured as date columns? Unlike Excel, in Smartsheet columns may be configured for different data types. Double click on the column header to see what's available.

Can you please copy your complete formula here?

Greetings

Stefan

Dominique

Yes both are configured as date columns.

I tried a few variants to see if anything helped:

=IF(Packing LL@755 - Requested Date Customer@755 < 4, "1", "0")

=IF(Packing LL@row - Requested Date Customer@row < 4, "1", "0")

=IF(Packing LL@755 - Requested Date Customer@755 < 4; "1"; "0")

Greetings, Dominique

Stefan

Hi Dominique,

this time it's another error ;-)

=IF(Packing LL@row - Requested Date Customer@row < 4, "1", "0") does not work because of your column name.

Whenever you use some special characters or spaces in the names of your columns, you need to put those names in square brackets.

This should work:

=IF([Packing LL]@row - [Requested Date Customer]@row < 4, "1", "0") does not work because of your column name.

Hope this helps

Stefan

Stefan

Sorry, copy&paste failure,

=IF([Packing LL]@row - [Requested Date Customer]@row < 4, "1", "0")

should work.

Dominique

Yes, it is working now!

Thank you Stefan

Stefan

Pleasure :-)

Dominique

Hi Stefan,

I do have a quick question. There are some cells in the column of Packing LL that are not filled

but the formula gives it a 1 anyway. Should I change something in the formula?

Greetings,

Dominique

Stefan

Hi Dominique,

a question of data quality. In the first place you should make sure that no date is missing and maybe it's not wrong to have a visual marker for missing data too.

In the meantime you may use an extended formula, which checks if any of the date cells is empty and in these cases leaves the 4orless cell empty.

=IF(OR([Packing LL]@row = "", [Requested Date Customer]@row = ""), "", IF([Requested Date Customer]@row - [Packing LL]@row < 4, "1", "0"))

Hope this helps

Stefan