Data Shuttle Date and Timestamp Issue

sheetsmartsarah

Hi,

I'm running into an issue as described in this post, but with Data Shuttle: Timestamps only match selected date format when clicked on — Smartsheet Community

I configured a Data Shuttle workflow to replace all rows when a .csv file is attached to the target sheet.

The attachment has a Timestamp column with a Date and Time. I have the column set to a Date column, restricted to dates only. However, it includes the time.

See rows 2 and 3 include "5:53". When I clicked into row 1, it removed the time.

I need to remove the time on all rows. I have thousands of rows and clicking into each of them would be a waste of time.

How can I fix this to automatically format properly?

Jake Gustafson

Thanks for all the support. Here's what I did for a workaround that finally got me to what I wanted. I didn't want to have to make any edits to the raw input file since that's something coming in daily and I didn't want to have to do any extra processing with it aside from saving it so Data Shuttle could ingest it into my Smartsheet with all the column formulas doing all the 'heavy lifting.'

I have essentially two instances where I needed to do the same thing:

1. Raw Data Column: Closed Date/Time = 12/19/2023 10:30
2. Smartsheet Helpers:
   1. Closed Year: =RIGHT(LEFT([Closed Date/Time]@row, FIND(" ", [Closed Date/Time]@row) - 1), 4)
   2. Closed Month: =LEFT([Closed Date/Time]@row, FIND("/", [Closed Date/Time]@row) - 1)
   3. Closed Day: =MID([Closed Date/Time]@row, FIND("/", [Closed Date/Time]@row) + 1, FIND("/", [Closed Date/Time]@row, FIND("/", [Closed Date/Time]@row) + 1) - FIND("/", [Closed Date/Time]@row) - 1)
   4. Closed Time: =RIGHT([Closed Date/Time]@row, LEN([Closed Date/Time]@row) - FIND(" ", [Closed Date/Time]@row))
3. New Date formatted column: Closed Date: =DATE(VALUE([Closed Year]@row), VALUE([Closed Month]@row), VALUE([Closed Day]@row))

Now I can use that 'Closed Date' to do some reporting tasks. Part of the struggle was the month and day fields being inconsistent with 1 or 2 digits and same with the hour piece of the time. So, what I have in place now accounts for that (as far as my limited testing has gone). I'm going to have fresh data coming in later this week, so I've got December 2023 data looking good and I expect my February 2024 data to do the same.

Thanks to everyone who has contributed to help workaround this input data issue.

sheetsmartsarah

I was able to resolve the issue by choosing a different data type in the .csv, but I don't want to have to manipulate a file each time prior to uploading to Smartsheet.

If anyone knows how to avoid this issue directly within Smartsheet, I'd appreciate the insight!

Danielle Wilson

@sheetsmartsarah You could probably make a new column with a formula to scrape off the time - something like: =LEFT(Timestamp@row, FIND(" ", Timestamp@row))

Jake Gustafson

https://community.smartsheet.com/discussion/comment/412719#Comment_412719

I'm running into the same issue where my raw data (in CSC) that I'm uploading using Data Shuttle has the full Date/Time Stamp, which I'm fine with, but then need to pull in that Date only for reporting purposes since the full value is only text. The formula suggested by @Danielle Wilson is only giving me a single value (i.e., 1). Here is what I'm using right now.

=LEFT([Closed Date/Time]@row, 10)

I'm grabbing the 10 digits, however when single digit months and days are used, then I have an issue because I no longer have 10 characters.

Does formatting that original timestamp column as a Date provide any benefit when trying to extract a date only from that?

Danielle Wilson

Hi @Jake Gustafson the formula I suggested breaks the time and date provided at the space - if you do have a space between the date and time, e.g. 9/21/2023 5:53 it would dynamically break wherever the space was to give you 9/21/2023 (and therefore would be flexible regardless of character length). If you do have a date and time in this format, it would work to do =LEFT([Closed Date/Time]@row, FIND(" ", [Closed Date/Time]@row)) otherwise you would probably have to use something else. Do you have an example of a value you're trying to work with?

Jake Gustafson

I'm running into an issue with the new column not registering as a date. The Column Properties indicate it is a Date, the data displays like it's a date. However, when I try writing formulas either in the Summary Fields or Filters, I am unable to return values. Is there something more that has to be done to allow these transposed timestamp values to actually behave like true Dates?

sheetsmartsarah

That's the same problem I had @Jake Gustafson . I can parse it like Danielle shared, but the cell data isn't registering as a date, even when it's written like a date and the column is set to the Date type. 😕 The quickest workaround for me was to set the column in my original data file to the "Short Date" data type before uploading. An annoying step, but the best workaround I found.

Jake Gustafson

Thanks for all the support. Here's what I did for a workaround that finally got me to what I wanted. I didn't want to have to make any edits to the raw input file since that's something coming in daily and I didn't want to have to do any extra processing with it aside from saving it so Data Shuttle could ingest it into my Smartsheet with all the column formulas doing all the 'heavy lifting.'

I have essentially two instances where I needed to do the same thing:

1. Raw Data Column: Closed Date/Time = 12/19/2023 10:30
2. Smartsheet Helpers:
   1. Closed Year: =RIGHT(LEFT([Closed Date/Time]@row, FIND(" ", [Closed Date/Time]@row) - 1), 4)
   2. Closed Month: =LEFT([Closed Date/Time]@row, FIND("/", [Closed Date/Time]@row) - 1)
   3. Closed Day: =MID([Closed Date/Time]@row, FIND("/", [Closed Date/Time]@row) + 1, FIND("/", [Closed Date/Time]@row, FIND("/", [Closed Date/Time]@row) + 1) - FIND("/", [Closed Date/Time]@row) - 1)
   4. Closed Time: =RIGHT([Closed Date/Time]@row, LEN([Closed Date/Time]@row) - FIND(" ", [Closed Date/Time]@row))
3. New Date formatted column: Closed Date: =DATE(VALUE([Closed Year]@row), VALUE([Closed Month]@row), VALUE([Closed Day]@row))

Now I can use that 'Closed Date' to do some reporting tasks. Part of the struggle was the month and day fields being inconsistent with 1 or 2 digits and same with the hour piece of the time. So, what I have in place now accounts for that (as far as my limited testing has gone). I'm going to have fresh data coming in later this week, so I've got December 2023 data looking good and I expect my February 2024 data to do the same.

Thanks to everyone who has contributed to help workaround this input data issue.

sheetsmartsarah

https://community.smartsheet.com/discussion/comment/417941#Comment_417941

This is great @Jake Gustafson ! Thanks for sharing. I'm going to try it out on my sheets 😊

sheetsmartsarah

I just implemented this and it worked for me! So far so good.