Formula from Excel to Smartsheet - testing for dates

Alex Br

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")))))))), "")

SoS | Dan Palenchar

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")))))))), "")