Date with Time uploading in Data Shuttle comes only with Date
Hi Everyone,
I'm uploading Input excel having column value "6/1/2022 1:22:23 AM". But after uploading into SmartSheet, it comes only date "2022-06-01".
I need exact format which is in my Input file.
Anyone help me on this.
Thanks,
Sandhiya P
Best Answer
-
Hi @Sandhiya07
Smartsheet Date columns can only house Dates, there currently isn't a Time function in Smartsheet. (System Columns can include timestamps, however these cannot be updated by Data Shuttle).
If you need to see the time, make sure to map this column to a Text/Number column in Smartsheet so that all of the text will appear.
Let me know if that works for you!
Cheers,
Genevieve
Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
Answers
-
Hi @Sandhiya07
Smartsheet Date columns can only house Dates, there currently isn't a Time function in Smartsheet. (System Columns can include timestamps, however these cannot be updated by Data Shuttle).
If you need to see the time, make sure to map this column to a Text/Number column in Smartsheet so that all of the text will appear.
Let me know if that works for you!
Cheers,
Genevieve
Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
-
Hi Genevieve,
I am encountering the same issue. I have set the column type to 'Text/Number' and the import via Data Shuttle is set to 'Auto', however it is still not pulling the time.
I need to be able to sort by this submission date and time.
Many thanks for your help!
jcl
ORIGINAL DATA
TEST IN SS
VIEW OF DS SETUP
-
Hi Genevieve,
Thanks - I'd already tested with all three options - Auto, Text, and Number. It didn't help, regretfully.
-
Hi Genevieve, the sheet cannot be saved as CSV because it is a workbook with multiple sheets. Data Shuttle is distributing the data out from each of these Excel sheets to their respective sheets in SS. Therefore the import source file is in XLSX.
Cheers, jcl
-
Hey @jchalig
What is your column set to in the source data, the XLSX file? I'm trying to replicate what you're seeing, but as long as the import is coming into a Text column it should retain all of the text from the source file.
Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
-
Hi @Genevieve P. thanks for the continued efforts on this.
1) Excel is in a date/time format.
2) I've ensured that the DS is set to text, as is the column in the target sheet. I've also refreshed the columns and run a full import to see if anything changes. Nadda.
I'm guessing it's got something to do with the source format at this point. This particular file is an XLSX downloaded from a Google Sheet. Regretfully there is no way around this.
Let me know what else I can provide to help!
-
Hi @jchalig
Thank you for this additional information! I can replicate what you're seeing now when the file is originally a Google Sheet. When testing the file in an XLSX format, I adjusted the dates to be text out of curiosity. This came through as a set of numbers.
According to the web, Google Sheets uses a sequential database of numbers to store dates to be able to perform calculations on them. I believe that Data Shuttle is only able to read the date portion from that Google Sheet number calculation during the import, since Smartsheet does not have time built in, since the source data can't be converted to Text (whereas XLSX dates can be text instead of Date/Time during import).
To get around this, I added a column to my XLSX document after it was generated with a formula converting it:
=TEXT(A1, "DD/MM/YYY HH:MM")
A converted column pulled through both the Date and Time in Data Shuttle as it was then seen as text! I don't know if your process would allow for additional columns and formulas in the source data, but if it does, let me know if this has worked for you.
Cheers,
Genevieve
Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
-
Brilliant work, Genevieve. I will give it a go to see if it works. No news is good news! Thanks again for your help.
-
Hi @Genevieve P.,
I have this exact issue as well. Is the only option still to open the xlsx sheet and create the new column with this function [ =TEXT(A1, "DD/MM/YYY HH:MM") ] so the time can be imported into DS correctly? To me, that defeats the purpose of DS automating this for me each morning.
Interestingly enough, if I import the xlsx as a new sheet not using DS, it lists the times only.
However, when I use DS to import it, it lists the date only.
In my situation, all I really need is the time. There is another column with date on my sheet...
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.1K Get Help
- 444 Global Discussions
- 142 Industry Talk
- 472 Announcements
- 5K Ideas & Feature Requests
- 83 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 489 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives