Dateonly gives #invalid Data Type
[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?
Best Answer
-
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!
Answers
-
I should have mentioned [Received Time] and [Recieved Date] are both Date columns.
-
@Scott Saxton how are the Received Time and Published Time recorded? Also is the NAXML(1) a concatenated formula or a static value?
-
Garrett, I'm not sure what you mean by "recorded." NAXML(1) is just a static value that was copied and pasted into place.
-
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!
-
Yes, that did do the trick! I wonder why LEFT works and DATEONLY does not. Thank you, Garrett!
-
@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.
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives