format date and compare it
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
Best Answer
-
Make sure Helper Date is a date type column.
Answers
-
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)))))
-
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
-
Make sure Helper Date is a date type column.
-
Thanks a lot Paul!
It works :-)
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 434 Global Discussions
- 136 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 64 Community Job Board
- 481 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!