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.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives