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

• ✭✭✭✭✭✭
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!