Importing Dates with Data Uploader

Katherine Britt

The source data that I download to an excel file from the external system is in this format " 11/2/2020 10:59 AM". Smartsheet does not recognize this as a date when imported into a date cell. I am using the dates to chart year over year data. The only way I've been able to get it to work is to change the column to a text column, save, then turn it into a date column. Then it changes to the 11/2/2020 format. This defeats the purpose of having the data automatically upload using the data uploader. I still have to manipulate the data.

Does anyone know how to solve this problem? Any help would be greatly appreciated.

Katherine Britt

I don't know what was going on, but I broke up each part of the formula, tested each function separately, then put it back together. Now it works! Thank you for all of your help. I don't know why it wasn't working. FYI... the day and month were transposed in the month formula. But that shouldn't have kept it from working. I never could have done this without you. Thanks again!! The final formula is below...

=IF(AND(FIND("/", [Text Date]@row) = 2, FIND(" ", [Text Date]@row) = 9), DATE(VALUE(RIGHT(LEFT([Text Date]@row, 8), 4)), VALUE(LEFT([Text Date]@row, 1)), VALUE(RIGHT(LEFT([Text Date]@row, 3), 1))), IF(AND(FIND("/", [Text Date]@row) = 3, FIND(" ", [Text Date]@row) = 10), DATE(VALUE(RIGHT(LEFT([Text Date]@row, 9), 4)), VALUE(LEFT([Text Date]@row, 2)), VALUE(RIGHT(LEFT([Text Date]@row, 4), 1))), IF(AND(FIND("/", [Text Date]@row) = 2, FIND(" ", [Text Date]@row) = 10), DATE(VALUE(RIGHT(LEFT([Text Date]@row, 9), 4)), VALUE(LEFT([Text Date]@row, 1)), VALUE(RIGHT(LEFT([Text Date]@row, 4), 2))), IF(AND(FIND("/", [Text Date]@row) = 3, FIND(" ", [Text Date]@row) = 11), DATE(VALUE(RIGHT(LEFT([Text Date]@row, 10), 4)), VALUE(LEFT([Text Date]@row, 2)), VALUE(RIGHT(LEFT([Text Date]@row, 5), 2)))))))

Genevieve P.

Hi @Katherine Britt

The way I would personally work around needing to update the column properties would be to have a Date Column in the sheet with a Formula looking into this Text Column to grab the date from here.

That said, it would be fairly complex formula based on the format coming from your source sheet. It looks like you could possibly have 4 types of text-dates coming through, is that correct?

Single Digits: 1/2/2020 10:59 AM

Double Digit Day, Single DigitMonth: 11/2/2020 10:59 AM

Single Digit Day, Double Digit Month: 1/12/2020 10:59 AM

Double Digits: 11/12/2020 10:59 AM

Because of this, we would need 4 separate formulas. Each formula would be based on the individual scenario above, then you would embed them all in IF statement so that the formula runs through the possible options and outputs a date depending on which statement is true.

1 . Find the numbers

To create a date, we'll use the DATE function.

=DATE(year, month, day)

To find which number in amongst that string to grab to represent each part of the date, we'll use a combination of LEFT and RIGHT. Then we'll also need to use the VALUE function to make sure it's numerical.

Here is the formula if we were ONLY looking at your original date, 11/2/2020 10:59 AM

=DATE(VALUE(RIGHT(LEFT([Text Date]@row, 9), 4)), VALUE(RIGHT(LEFT([Text Date]@row, 6), 1)), VALUE(LEFT([Text Date]@row, 2)))

2 . IF checking for variable

Now, to distinguish that this is a date coming in with a Double Digit Day and a Single Digit Month, we would use this IF statement:

=IF(AND(FIND("/", [Text Date]@row) = 3, FIND(" ", [Text Date]@row) = 10), "Yes")

^This checks to see if the "/" is in the third place of the text using the FIND function, meaning that there are two numbers for the day, not one. Then it checks to find where the space is after the year, before the time, to see if there is only one digit for the Month (because that would place the space in the 10th position). Does that make sense?

Then instead of outputting "yes", we want to paste the previous DATE formula into the True portion of the IF statement:

IF(AND(FIND("/", [Text Date]@row) = 3, FIND(" ", [Text Date]@row) = 10), DATE(VALUE(RIGHT(LEFT([Text Date]@row, 9), 4)), VALUE(RIGHT(LEFT([Text Date]@row, 6), 1)), VALUE(LEFT([Text Date]@row, 2))),

3 . Final Formula

OK so I've done that for each of your 4 scenarios. Then I've put it all together in one giant IF statement. This is what you'd put in that helper Date-Type column to convert all your text dates into Smartsheet Dates:

=IF(AND(FIND("/", [Text Date]@row) = 2, FIND(" ", [Text Date]@row) = 9), DATE(VALUE(RIGHT(LEFT([Text Date]@row, 8), 4)), VALUE(RIGHT(LEFT([Text Date]@row, 3), 1)), VALUE(LEFT([Text Date]@row, 1))), IF(AND(FIND("/", [Text Date]@row) = 3, FIND(" ", [Text Date]@row) = 10), DATE(VALUE(RIGHT(LEFT([Text Date]@row, 9), 4)), VALUE(RIGHT(LEFT([Text Date]@row, 6), 1)), VALUE(LEFT([Text Date]@row, 2))), IF(AND(FIND("/", [Text Date]@row) = 2, FIND(" ", [Text Date]@row) = 10), DATE(VALUE(RIGHT(LEFT([Text Date]@row, 9), 4)), VALUE(RIGHT(LEFT([Text Date]@row, 4), 2)), VALUE(LEFT([Text Date]@row, 1))), IF(AND(FIND("/", [Text Date]@row) = 3, FIND(" ", [Text Date]@row) = 11), DATE(VALUE(RIGHT(LEFT([Text Date]@row, 10), 4)), VALUE(RIGHT(LEFT([Text Date]@row, 5), 2)), VALUE(LEFT([Text Date]@row, 2)))))))

^This would then be set as a column formula, so it automatically updates as new rows are added or changed.

Other members in the Community may have a more succinct version of this formula, but that's what I would do! Let me know if this would work for you.

Cheers,

Genevieve

Katherine Britt

I will try it out and let you know! THANK YOU THANK YOU for taking the time out to help!🤗

Genevieve P.

No problem! I've tested this on my sheet but please do let me know if this works for you, or if you have any questions about how it's built. I'm happy to break it down or go into more detail.

Cheers!

Katherine Britt

I am getting an #INVALID VALUE error.

I broke down each if statement and typed out what it would return... I can't figure it out.

Genevieve P.

Hi @Katherine Britt

What type of column are you using to input the formula? It will need to be a Date Type of column in order to return a date.

Katherine Britt

Yes, I entered the formula into a date column.

Katherine Britt

I don't know what was going on, but I broke up each part of the formula, tested each function separately, then put it back together. Now it works! Thank you for all of your help. I don't know why it wasn't working. FYI... the day and month were transposed in the month formula. But that shouldn't have kept it from working. I never could have done this without you. Thanks again!! The final formula is below...

=IF(AND(FIND("/", [Text Date]@row) = 2, FIND(" ", [Text Date]@row) = 9), DATE(VALUE(RIGHT(LEFT([Text Date]@row, 8), 4)), VALUE(LEFT([Text Date]@row, 1)), VALUE(RIGHT(LEFT([Text Date]@row, 3), 1))), IF(AND(FIND("/", [Text Date]@row) = 3, FIND(" ", [Text Date]@row) = 10), DATE(VALUE(RIGHT(LEFT([Text Date]@row, 9), 4)), VALUE(LEFT([Text Date]@row, 2)), VALUE(RIGHT(LEFT([Text Date]@row, 4), 1))), IF(AND(FIND("/", [Text Date]@row) = 2, FIND(" ", [Text Date]@row) = 10), DATE(VALUE(RIGHT(LEFT([Text Date]@row, 9), 4)), VALUE(LEFT([Text Date]@row, 1)), VALUE(RIGHT(LEFT([Text Date]@row, 4), 2))), IF(AND(FIND("/", [Text Date]@row) = 3, FIND(" ", [Text Date]@row) = 11), DATE(VALUE(RIGHT(LEFT([Text Date]@row, 10), 4)), VALUE(LEFT([Text Date]@row, 2)), VALUE(RIGHT(LEFT([Text Date]@row, 5), 2)))))))

Genevieve P.

Hi @Katherine Britt

I'm so glad you got it working! There's likely a more concise way to write this formula but this made the most logical sense to me, step-by-step.

Thanks for posting your final solution!

Cheers,

Genevieve

Ali Haider

Hi @Katherine Britt 

Just directly copy and paste the data related to the date from excel to Smartsheet.

I have do the same. I hope this will work for you.

Regards,

Ali