Formula from Excel to Smartsheet - testing for dates

Options

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

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    edited 07/03/24
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!