Formula from Excel to Smartsheet - testing for dates
Hi,
I'm having a little difficulty in transferring the following from Excel, where in Excel, we were able to use 1 and 0 to test whether a cell formatted for a date contained a value or not. I understand that that is because Excel converts 1 as 01-Jan-1900, however I'm uncertain how to then convert the below into Smartsheet format. Should I be using ISBLANK and ISDATE, and if so, would it be okay to get some advice how to amend the formula to use it please?
=IFERROR(IF([Ordered by]@row = "Order Issue", "ORDER ISSUE", IF([Ordered by]@row = "Cancelled", "CANCELLED", IF(AND([Estimated Delivery Date]@row > [Date Required By]@row, [Date Received]@row = 0, [Date Required By]@row <> 0), "At Risk", IF([Date Received]@row > 1, "Received", IF(PO@row > 1, "PO Generated", IF(PR@row > 1, "PR Submitted", IF(AND((NETDAYS([Date Required By]@row - [Date Requested]@row)) <= 14, [Date Required By]@row > 1), "High Priority", IF([Date Requested]@row = 0, "", "Requested")))))))), "")
Answers
-
Hello @Alex Br
Yes, you can use the ISDATE() function, this is how Smartsheet checks for dates. You can also use NOT(ISDATE()) to check if something is NOT a date specifically, but ISBLANK() would work just as well unless there may be a non date value that can appear (for example, if somebody wrote some random text or numbers).
So if you want to check if something is a date/ not a date:
=IF(ISDATE([Date Column]@row), <value if it is a date>, <value if not a date>)
=IF(NOT(ISDATE([Date Column]@row)), <Value if not a date>, <Value if a date>)
When using IFDATE() and/or NOT() make sure you have close the parentheses after the date column.
Also, your NETDAYS() portion is formatted incorrectly. You don't use a mathematical operator (see below)
You have:
NETDAYS([Date Required By]@row - [Date Requested]@row))
It should be:
NETDAYS([Date Requested]@row, [Date Required By]@row)
I have edited your formula below with revisions in bold. I have not tested this myself so it's possible I missed a parentheses, if it's not working as is hopefully it helps get you close enough to finalize it yourself!
=IFERROR(IF([Ordered by]@row = "Order Issue", "ORDER ISSUE", IF([Ordered by]@row = "Cancelled", "CANCELLED", IF(AND([Estimated Delivery Date]@row > [Date Required By]@row,
NOT(ISDATE([Date Received]@row))
,
ISDATE([Date Required By]@row)
, "At Risk", IF(
ISDATE([Date Received]@row)
, "Received", IF(PO@row > 1, "PO Generated", IF(PR@row > 1, "PR Submitted", IF(AND((
NETDAYS([Date Requested]@row, [Date Required By]@row)
) <= 14, [Date Required By]@row > 1), "High Priority", IF(
NOT(ISDATE([Date Requested]@row))
, "", "Requested")))))))), "")
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!