Extracting time from a date field
Hi, I'm using Data Shuttle to import an Excel file that has a combined date/time.
Smartsheet/Data Shuttle is recognizing the date, and bringing the field in as only the date. I've tried bringing it in to both date and text columns, same result. I've tried importing manually into Smartsheet (not using Data Shuttle) and same result.
Because there are multiple occurrences on the same date and times vary I've got to capture that info.
Suggestions?
Thanks!
Best Answer
-
Hi @Terry Lewis
Since Smartsheet does not have a Time function or column type, you would want to map this data to a Text/Number column. In testing on my end, mapping this type of value to a Text/Number column brought through the entire cell contents.
If you're only seeing the Date come through when mapping to a Text/Number column, would you be able to upload a copy of the file with dummy data that I could test with?
Thanks!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi @Terry Lewis
Since Smartsheet does not have a Time function or column type, you would want to map this data to a Text/Number column. In testing on my end, mapping this type of value to a Text/Number column brought through the entire cell contents.
If you're only seeing the Date come through when mapping to a Text/Number column, would you be able to upload a copy of the file with dummy data that I could test with?
Thanks!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks! I was able to get that to work. I had to delete and rebuild the columns and the Data Shuttle work flow to kind of get back to a "clean slate," but it did work then.
Appreciate you getting me on the right track!
T.
-
Will a Time function be introduced in the near future? This would be most useful.
Regarding the date, I have tried to extract the date from imported date and time (31/10/2022 20:10:00) and returned the correct date to a new column.
Then formatted as a date column 31/10/22 but the lookup does not recognise the data and returns #NOMATCH. The only way I have found is to manually paste the dates in to my column. Any ideas please?
-
Hi @NJCunliffe
What type of column is your "Input Data" column? If you have this set as a Date type of column, it should automatically strip out the time at the end and only show the date (which you can then use in a VLOOKUP or INDEX(MATCH).
However, if you're importing this into a text column, then the LEFT function is still reading the values as text. This means your Vlookup won't be able to match text to a date field.
Instead, you would want to use a DATE function to convert your text into dates. Here are other Community threads with examples of this type of formula:
- Converting a drop down single select list of dates into a date I can use with Calendar App
- Extracting a date from a text field
- Text to Date Field
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
The data input is text as per the copy and paste from excel. If I change col to a date then I loose the time part which is needed for qualifying opinions to look at later when explaining irregularities.
31/10/2022 20:40:00 is how it looks as text
31/10/22 is how it looks if I choose col type date
Modified formula is as follows
=DATE(VALUE(LEFT([Input data]@row, 4)), VALUE(MID([Input data]@row, 6, 2)), VALUE(MID([Input data]@row, 9, 2)))
Still returns an error
#INVALID VALUE
If I format as a date, the lookup works but then I lose the time intelligence. Any mistakes in my syntax or other ideas please?
-
=DATE(VALUE(LEFT([Input data]@row, 2)), VALUE(MID([Input data]@row, 4, 2)), VALUE(MID([Input data]@row, 9, 2)))
noticed a mistake in left function but still in error
-
Hi @NJCunliffe
The DATE Function needs data formatted like so:
DATE(YYYY, MM, DD)
It looks like you're using VALUE, LEFT, and MID to pull data the opposite way around (dd/mm/yy)
Try:
=DATE(VALUE(MID([Input Data]@row, 7, 4)), VALUE(MID([Input Data]@row, 4, 2)), VALUE(LEFT([Input Data]@row, 2)))
Notice that for the YYYY we're grabbing 4 values, not 2, and starting from the 7th position. Let me know if this works!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives