Extracting time from a date field

Terry Lewis

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!

Genevieve P.

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

Genevieve P.

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

Terry Lewis

Hi @Genevieve P.

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.

NJCunliffe

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?

Genevieve P.

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

NJCunliffe

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?

NJCunliffe

=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

Genevieve P.

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