日期与时间上传数据航天飞机是辊筒y with Date

Sandhiya07
Sandhiya07 ✭✭

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

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    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 aText/Numbercolumn in Smartsheet so that all of the text will appear.

    Let me know if that works for you!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    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 aText/Numbercolumn in Smartsheet so that all of the text will appear.

    Let me know if that works for you!

    Cheers,

    Genevieve

  • jchalig
    jchalig ✭✭

    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

    Screen Shot 2023-07-05 at 11.19.22 am.png


    TEST IN SS

    Screen Shot 2023-07-05 at 11.19.38 am.png

    VIEW OF DS SETUP

    Screen Shot 2023-07-05 at 11.20.36 am.png
    Screen Shot 2023-07-05 at 11.20.58 am.png


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi@jchalig

    Try adjusting your Data Shuttle set up to identify the values asTextinstead of a Date (Auto).


    In your screen capture you show this:

    Screenshot 2023-07-05 at 18.53.49.png


    Screenshot 2023-07-05 at 18.55.34.png


    Try to turn that "Auto" into "Text:







    Cheers,

    Genevieve

  • jchalig
    jchalig ✭✭

    Hi Genevieve,

    Thanks - I'd already tested with all three options - Auto, Text, and Number. It didn't help, regretfully.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hey@jchalig

    How is your source file configured? Can you try importing a different file type, for example a CSV?

    Thanks!

    Genevieve

  • jchalig
    jchalig ✭✭

    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

  • Genevieve P.
    Genevieve P. Employee Admin

    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.

  • jchalig
    jchalig ✭✭

    Hi@Genevieve P.thanks for the continued efforts on this.

    1) Excel is in a date/time format.

    Screen Shot 2023-07-12 at 11.03.53 am.png


    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!

  • Genevieve P.
    Genevieve P. Employee Admin

    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.

    Screenshot 2023-07-13 at 21.17.17.png


    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 Dateand在航天飞机,因为它被视为文本的数据!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

  • jchalig
    jchalig ✭✭

    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 @Matthew LeMasonry<\/a> <\/p>

No, it currently isn't possible to share the Data Shuttle workflow configuration with another account. The set-up for that workflow is associated with one account. (Please let our Product team know of your feature request by filling in this form, here!)<\/a><\/p>

That said, many team members will be able to trigger<\/em> that workflow, depending on what that trigger is. For example, if the workflow is based on an attachment, any user can upload the attachment for the workflow. Does that make sense?<\/p>

If you are looking to transfer workflows because a user has left the organization, you could potentially update the Primary email address associated with the account that owns the configurations. This will allow you to gain access to the Data Shuttle workflows from the new email. Keep in mind this would need to be an email that does not currently have a Smartsheet account. See: Admin Center: Add, Edit, and Delete Individual Users with User Management<\/a><\/p>

Cheers,<\/p>

Genevieve<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":343,"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations","allowedDiscussionTypes":["discussion","question"]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":2},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":107864,"type":"question","name":"Anyone else having issues with Calendar App not Downloading PDF of entire calendar?","excerpt":"I have been on the phone with Smartsheet support this week but we have been unable to figure out what could be causing this issue. I am finding that the Calendar app is only Downloading one page of a PDF, cutting off any information that would be on the subsequent pages. Example below You can see the bottom four lines,…","snippet":"I have been on the phone with Smartsheet support this week but we have been unable to figure out what could be causing this issue. I am finding that the Calendar app is only…","categoryID":343,"dateInserted":"2023-07-20T16:17:45+00:00","dateUpdated":null,"dateLastComment":"2023-07-25T16:28:06+00:00","insertUserID":156581,"insertUser":{"userID":156581,"name":"Macorne","url":"https:\/\/community.smartsheet.com\/profile\/Macorne","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-27T17:22:01+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":156581,"lastUser":{"userID":156581,"name":"Macorne","url":"https:\/\/community.smartsheet.com\/profile\/Macorne","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-27T17:22:01+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":45,"score":null,"hot":3380173551,"url":"https:\/\/community.smartsheet.com\/discussion\/107864\/anyone-else-having-issues-with-calendar-app-not-downloading-pdf-of-entire-calendar","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107864\/anyone-else-having-issues-with-calendar-app-not-downloading-pdf-of-entire-calendar","format":"Rich","tagIDs":[449],"lastPost":{"discussionID":107864,"commentID":386931,"name":"Re: Anyone else having issues with Calendar App not Downloading PDF of entire calendar?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386931#Comment_386931","dateInserted":"2023-07-25T16:28:06+00:00","insertUserID":156581,"insertUser":{"userID":156581,"name":"Macorne","url":"https:\/\/community.smartsheet.com\/profile\/Macorne","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-27T17:22:01+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/JTO6PBQV26S0\/pdf-cutoff-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"PDF cutoff.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-25T16:27:54+00:00","dateAnswered":"2023-07-25T12:38:16+00:00","acceptedAnswers":[{"commentID":386813,"body":"

Hey @Macorne<\/a> <\/p>

I checked in with our Support team and they confirmed that two other users had reported a similar behaviour as well. Thank you for your patience and persistence as we looked into this!<\/p>

We've since had confirmation that there has been a fix in the Print behaviour for the Calendar app as of this weekend. Please try again and let us know if this has now been resolved for you. <\/p>

Cheers,<\/p>

Genevieve<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":343,"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations","allowedDiscussionTypes":["discussion","question"]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":449,"urlcode":"calendar-app","name":"Calendar App"}]},{"discussionID":72863,"type":"question","name":"Document Builder - date format?","excerpt":"I am trying to generate certificates of completion with document builder and include the date of conferral. No matter how I enter the date into a date field (my preference is \"Friday, January 1, 2021\" as the format), it always enters in the 1\/1\/21 format. Any ideas for getting document builder to fill it correctly?","snippet":"I am trying to generate certificates of completion with document builder and include the date of conferral. No matter how I enter the date into a date field (my preference is…","categoryID":343,"dateInserted":"2020-11-04T23:06:36+00:00","dateUpdated":"2022-06-14T14:37:26+00:00","dateLastComment":"2023-07-21T23:09:55+00:00","insertUserID":128195,"insertUser":{"userID":128195,"name":"Robin Sugiura","url":"https:\/\/community.smartsheet.com\/profile\/Robin%20Sugiura","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-07T19:16:47+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":91566,"lastUserID":150391,"lastUser":{"userID":150391,"name":"Rebecca Ortinez","url":"https:\/\/community.smartsheet.com\/profile\/Rebecca%20Ortinez","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-26T18:48:36+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":14,"countViews":789,"score":1,"hot":3294520891,"url":"https:\/\/community.smartsheet.com\/discussion\/72863\/document-builder-date-format","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/72863\/document-builder-date-format","format":"Rich","lastPost":{"discussionID":72863,"commentID":386528,"name":"Re: Document Builder - date format?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386528#Comment_386528","dateInserted":"2023-07-21T23:09:55+00:00","insertUserID":150391,"insertUser":{"userID":150391,"name":"Rebecca Ortinez","url":"https:\/\/community.smartsheet.com\/profile\/Rebecca%20Ortinez","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-26T18:48:36+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-11-05T09:58:05+00:00","dateAnswered":"2020-11-04T23:19:01+00:00","acceptedAnswers":[{"commentID":267977,"body":"

I figured out a workaround -- if the date is entered in long form in a text column instead of a date column, it populates as entered. <\/p>

Don't know if there is another way to affect the formatting if populating from a date column.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":343,"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations","allowedDiscussionTypes":["discussion","question"]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":1},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=343&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-dateLastComment&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":626,"limit":3},"title":"Trending Posts","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending Posts