Dateonly gives #invalid Data Type

Scott Saxton

[Received Time] is pulling the first datetime from [NAXML(1)]. Then [Received Date] is trying to pull just the date from [Reveived Time]. I'm getting the #Invalid Date Type error and I'm guessing it is becasue it isn't seeing the value in the [Received Time] cell, just the formula?

Garrett Henke

@Scott Saxton

Ok since NAXML is a static value you can use it to break down the needed information. I used the same formula that you used to calculate the Received Time. I then used =LEFT([Received Time]@row, FIND(" ", [Received Time]@row)) to pull the date only from the Received Time value. I tried setting NAXML(1) as a Text/Number column and a Date column and both worked. Received Time and Received Date are set as date columns. Hope this helps!

Scott Saxton

I should have mentioned [Received Time] and [Recieved Date] are both Date columns.

Garrett Henke

@Scott Saxton how are the Received Time and Published Time recorded? Also is the NAXML(1) a concatenated formula or a static value?

Scott Saxton

Garrett, I'm not sure what you mean by "recorded." NAXML(1) is just a static value that was copied and pasted into place.

Garrett Henke

@Scott Saxton

Ok since NAXML is a static value you can use it to break down the needed information. I used the same formula that you used to calculate the Received Time. I then used =LEFT([Received Time]@row, FIND(" ", [Received Time]@row)) to pull the date only from the Received Time value. I tried setting NAXML(1) as a Text/Number column and a Date column and both worked. Received Time and Received Date are set as date columns. Hope this helps!

Scott Saxton

Yes, that did do the trick! I wonder why LEFT works and DATEONLY does not. Thank you, Garrett!

Garrett Henke

@Scott Saxton I believe the issue is that DATEONLY is specifically for date/time inputs. NAXML(1) is a string value and even through LEFT/FIND extracts what we perceive as a date/time value Smartsheet still thinks its a string. Therefore when you use DATEONLY on a string it returns an #INVALID DATA TYPE.

Or another possibility is that because you are using a formula on a formula the DATEONLY function is looking specifically at the formula and not the returned value.

Scott Saxton

I was pointing the DATEONLY at the [Received Time] cell which which I designated as a Date column so I'm guessing its because there is a formula in that cell.