Getting #Invalid Data Type error in seemingly simple formula

twarner

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:

mm/dd/yyy hh:mm

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:

1. 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.
2. 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".
   1. 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())

Any suggestions?

TIA - Tony

Jeff Reisman

@twarner You have maybe two options here.

Option 1: Fix the import. This is probably your best bet, if you can do it. I don't know anything about BI or Data Shuttle, but getting an actual date value in that field to start with would be key, because...

Option 2: is a little crazy. The concept I'm thinking of is using the FIND function to determine where in the date-time string the slashes are, so that you can extract the proper digits to use to construct a proper date value. If you have plenty of room to spare in your sheet, I'd say adding three helper columns ("Year", "Month", "Day") where you can collect the Year, Month, and Day values would make this easier. So let's see:

3/17/2022 15:36

We'll start with the Month value.

=IF(FIND("/", [Date Opened]@row) = 2, VALUE(LEFT([Date Opened]@row, 1)), IF(FIND("/", [Date Opened]@row) = 3, VALUE(LEFT([Date Opened]@row, 2)))

In English, if you find a slash in the date opened value at position 2, just give me the numeric value of the first character from the left, but if you find a slash at position 3, give me the number value of the first two characters from the left.

For the Day value, we'll do something similar except we'll use MID rather than LEFT, and we'll tell FIND where to start looking for a slash. You may have to change this a little if your date opened value uses single digit days (say 3/3/2022 instead of 3/03/2022)

=IF(FIND("/", [Date Opened]@row, 4) = 5, VALUE(MID([Date Opened]@row, 3, 2)), IF(FIND("/", [Date Opened]@row, 4) = 6, VALUE(MID([Date Opened]@row, 4, 2)))

In English, if you start at position 4 and find a slash at position 5, give me the numeric value of the two characters starting at position 3. If you start at position 4 and find a slash at position 6, give me the numeric value of the two characters starting at position 4.

Now Year:

=IF(FIND("/", [Date Opened]@row, 4) = 5, VALUE(MID([Date Opened]@row, 6, 4)), IF(FIND("/", [Date Opened]@row, 4) = 6, VALUE(MID([Date Opened]@row, 7, 4)))

In English, same as above, just getting the value of the four characters staring at position 6 or position 7.

So now you have three columns with numeric values for Year, Month, and Day. You can construct a valid date value in a date-type column like this:

=DATE(Year@row, Month@row, Day@row)

And THIS date value will compute when you want to subtract it from TODAY, or use NETWORKDAYS, etc, to get the number of days since.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/328518#Comment_328518

Now if you don't want to add those three helper columns, you can do this (and say a little prayer for it to work:)

=DATE(IF(FIND("/", [Date Opened]@row, 4) = 5, VALUE(MID([Date Opened]@row, 6, 4)), IF(FIND("/", [Date Opened]@row, 4) = 6, VALUE(MID([Date Opened]@row, 7, 4))), IF(FIND("/", [Date Opened]@row) = 2, VALUE(LEFT([Date Opened]@row, 1)), IF(FIND("/", [Date Opened]@row) = 3, VALUE(LEFT([Date Opened]@row, 2))), IF(FIND("/", [Date Opened]@row, 4) = 5, VALUE(MID([Date Opened]@row, 3, 2)), IF(FIND("/", [Date Opened]@row, 4) = 6, VALUE(MID([Date Opened]@row, 4, 2))))

😀

Jeff Reisman

@twarner You have maybe two options here.

Option 1: Fix the import. This is probably your best bet, if you can do it. I don't know anything about BI or Data Shuttle, but getting an actual date value in that field to start with would be key, because...

Option 2: is a little crazy. The concept I'm thinking of is using the FIND function to determine where in the date-time string the slashes are, so that you can extract the proper digits to use to construct a proper date value. If you have plenty of room to spare in your sheet, I'd say adding three helper columns ("Year", "Month", "Day") where you can collect the Year, Month, and Day values would make this easier. So let's see:

3/17/2022 15:36

We'll start with the Month value.

=IF(FIND("/", [Date Opened]@row) = 2, VALUE(LEFT([Date Opened]@row, 1)), IF(FIND("/", [Date Opened]@row) = 3, VALUE(LEFT([Date Opened]@row, 2)))

In English, if you find a slash in the date opened value at position 2, just give me the numeric value of the first character from the left, but if you find a slash at position 3, give me the number value of the first two characters from the left.

For the Day value, we'll do something similar except we'll use MID rather than LEFT, and we'll tell FIND where to start looking for a slash. You may have to change this a little if your date opened value uses single digit days (say 3/3/2022 instead of 3/03/2022)

=IF(FIND("/", [Date Opened]@row, 4) = 5, VALUE(MID([Date Opened]@row, 3, 2)), IF(FIND("/", [Date Opened]@row, 4) = 6, VALUE(MID([Date Opened]@row, 4, 2)))

In English, if you start at position 4 and find a slash at position 5, give me the numeric value of the two characters starting at position 3. If you start at position 4 and find a slash at position 6, give me the numeric value of the two characters starting at position 4.

Now Year:

=IF(FIND("/", [Date Opened]@row, 4) = 5, VALUE(MID([Date Opened]@row, 6, 4)), IF(FIND("/", [Date Opened]@row, 4) = 6, VALUE(MID([Date Opened]@row, 7, 4)))

In English, same as above, just getting the value of the four characters staring at position 6 or position 7.

So now you have three columns with numeric values for Year, Month, and Day. You can construct a valid date value in a date-type column like this:

=DATE(Year@row, Month@row, Day@row)

And THIS date value will compute when you want to subtract it from TODAY, or use NETWORKDAYS, etc, to get the number of days since.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/328518#Comment_328518

Now if you don't want to add those three helper columns, you can do this (and say a little prayer for it to work:)

=DATE(IF(FIND("/", [Date Opened]@row, 4) = 5, VALUE(MID([Date Opened]@row, 6, 4)), IF(FIND("/", [Date Opened]@row, 4) = 6, VALUE(MID([Date Opened]@row, 7, 4))), IF(FIND("/", [Date Opened]@row) = 2, VALUE(LEFT([Date Opened]@row, 1)), IF(FIND("/", [Date Opened]@row) = 3, VALUE(LEFT([Date Opened]@row, 2))), IF(FIND("/", [Date Opened]@row, 4) = 5, VALUE(MID([Date Opened]@row, 3, 2)), IF(FIND("/", [Date Opened]@row, 4) = 6, VALUE(MID([Date Opened]@row, 4, 2))))

😀

twarner

Jeff Reisman - Thank you for your insight! It took a bit of modification to deal with the format of the date data that is coming in, but your solution worked perfectly. I had to add one more IF statement to deal with 3 different possible sizes of the dates - to see where to start gathering the MONTH characters):

4/1/2022

10/1/2022 or 4/12/2022

10/12/2022

I managed to get it all working as one formula to find the age (in Working days) from Today:

=NETWORKDAYS(DATE(IF(FIND("/", [Date Opened]@row, 4) = 4, VALUE(MID([Date Opened]@row, 5, 4)), IF(FIND("/", [Date Opened]@row, 4) = 5, VALUE(MID([Date Opened]@row, 6, 4)), IF(FIND("/", [Date Opened]@row, 4) = 6, VALUE(MID([Date Opened]@row, 7, 4))))), IF(FIND("/", [Date Opened]@row) = 2, VALUE(LEFT([Date Opened]@row, 1)), IF(FIND("/", [Date Opened]@row) = 3, VALUE(LEFT([Date Opened]@row, 2)))), IF(FIND("/", [Date Opened]@row, 4) = 4, VALUE(MID([Date Opened]@row, 3, 1)), IF(FIND("/", [Date Opened]@row, 4) = 5, VALUE(MID([Date Opened]@row, 3, 2)), IF(FIND("/", [Date Opened]@row, 4) = 6, VALUE(MID([Date Opened]@row, 4, 2)))))), TODAY())

Thanks again!

Tony

Jeff Reisman

@twarner Nice work! This was fun to work on, because when the idea popped into my head I honestly had no idea if it would work. It was one of those straight-from-the-subconscious ideas that are usually harder to implement than I think they're going to be.

Glad it worked for you, have a great weekend.