format date and compare it

Christine Menke

Hello Smartsheet Experts,

I would like to compare a date in my column A with the date 10/12/2020. If it is before the date than it should show up as NEW.

The problem is that the dates "article creation date" have a different format (coming out of a system).

Here are the 2 formats I have in "article creation date";

2018-01-15

1/15/2018 10:10:17

I tried this but it's works only for the cell with this format 2018-01-15, for the other cell I get "invalid operation".

=IF([article creation date]@row < DATE(2020, 12, 1), "", "NEW")

Would be great if somebody could help me?

Thanks and Regards

Christine

Paul Newcome

Make sure Helper Date is a date type column.

Paul Newcome

I would suggest a helper date column that will convert every row to a usable date and then work off of the helper.

=IF(ISDATE([Article Creation Date]@row), [Article Creation Date]@row, DATE(VALUE(MID([Article Creation Date]@row, FIND("/", [Article Creation Date]@row, 4) + 1, 4)), VALUE(LEFT([Article Creation Date]@row, FIND("/", [Article Creation Date]@row) - 1)), VALUE(MID([Article Creation Date]@row, FIND("/", [Article Creation Date]@row) + 1, FIND("/", [Article Creation Date]@row, 4) - (FIND("/", [Article Creation Date]@row) + 1)))))

Christine Menke

Dear Paul,

thanks for looking into it.

but it's not working.

I realized when I change the date format it only change the dates of e.g. 1st row. Seems that 2. row it not a Date format, also if it looks like. Any other idea to transfer it to a real date?

Thanks for your help!

Christine

Paul Newcome

Make sure Helper Date is a date type column.

Christine Menke

Thanks a lot Paul!

It works :-)

Paul Newcome

Happy to help. 👍️