Getting #Invalid Data Type error in seemingly simple formula
Hi! I'm trying to get the # of days in the past a particular date is.
The date in question is in a DATE type column. It is being entered via Data Shuttle Upload from an Excel sheet that is an export of a BI system. The date in the Excel is of the following format:
The entire file, including this date column imports successfully into Smartsheet.
The formulas I've tried are:
=Today@row - [Date Opened]@row
=NETWORKDAYS([Date Opened]@row, TODAY())
=NETWORKDAYS([Date Opened]@row, Today@row) (where "Today@row" is just a helper column with "=TODAY()"
All of the above result in the #Invalid Data Type error.
In my troubleshooting, I've discovered a couple of things:
- that there must be something wrong with the date/time that is being imported, as even filtering that column does not appear to be recognizing the content as a Date. For instance, when filtering for rows with Date Opened "is in the past", nothing shows up, even though all the dates are in the past.
- When I double-click one of the Date Opened cells and hit "Enter", or just move off the cell, it changes the format of the content to be just a Date, with no time component. i.e. "3/17/2022 15:36" becomes "3/17/2022".
- After doing this "open the cell and closing the cell" action, all the Date functionality works just fine, including my formulas.
So it seems that the import of this date format is the issue, but it looks like a perfectly valid date/time format.
I've even tried using the DateOnly function to get rid of the time portion of the date, but get exactly the same results with the #Invalid Data Type error. i.e.
=NETWORKDAYS(DATEONLY([Date Opened]@row), TODAY())
TIA - Tony
Help Article Resources
Check out the Formula Handbook template!