Date Format

Drew Cooper

Hello,

The data that gets imported has date list with time (example: 11/20/2019 7:45am)

I only care about the month/day/year. Is there a way to have the extra data removed? In excel I use the delimited feature. Hoping Smartsheets can accomplish

Date no Time.PNG

Date with Time.PNG

this easier.

Paul Newcome

I was mistaken. I missed a "+ 1" after the FIND in the YEAR portion.

=DATE(VALUE(MID(Last@row, FIND("/", Last@row, 4) + 1, 4)), VALUE(LEFT(Last@row, FIND("/", Last@row) - 1)), VALUE(MID(Last@row, FIND("/", Last@row) + 1, FIND("/", Last@row, 4) - (FIND("/", Last@row) + 1))))

Drew Cooper

Nope - and that solved the issue.

Thanks Paul!!!

Paul Newcome

Is it going into a text/number column?

Drew Cooper

No it is going into a date column.

Andrée Starå

Hi @Drew Cooper

I hope you're well and safe!

Strange! It should only show the date in an ordinary date column.

What do you see If you double-click on the date?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Drew Cooper

This is how its being imported. 4/15/2021 10:23:48 PM(MST). I'm hoping there is an easy way to have it only import "4/15/2021"

Paul Newcome

It looks like it is pulling over a text value. You will need a formula to pull the date out of that into another date type column.

=DATE(VALUE(MID(Last@row, FIND("/", Last@row, 4), 4)), VALUE(LEFT(Last@row, FIND("/", Last@row) - 1)), VALUE(MID(Last@row, FIND("/", Last@row) + 1, FIND("/", Last@row, 4) - (FIND("/", Last@row) + 1))))

Drew Cooper

Thanks Paul. I attempted this and the new column is now returning an #INVALID VALUE Error. See attachments

Error.PNG

Formula.PNG

.

Paul Newcome

My apologies. The formula was written based off of the day always being two digits (02 instead of 2). Let me work on it a little bit, and I will get back to you.

Paul Newcome

I was mistaken. I missed a "+ 1" after the FIND in the YEAR portion.

=DATE(VALUE(MID(Last@row, FIND("/", Last@row, 4) + 1, 4)), VALUE(LEFT(Last@row, FIND("/", Last@row) - 1)), VALUE(MID(Last@row, FIND("/", Last@row) + 1, FIND("/", Last@row, 4) - (FIND("/", Last@row) + 1))))

Drew Cooper

Thanks Paul!! You are the MAN!!!!!!!!!!

Paul Newcome

Happy to help. 👍️

Drew Cooper

I need the same thing but my data for the date is now listed as 2021-5-25 11:00.

Could you please help me update the formula to get this to pull in like 5/25/2021.

Original Date being uploaded is listed as: 2021-5-25 11:00 (Column Name is Last)

Want to have a column that converts this to: 5/25/2021

Thanks!!

Paul Newcome

@Drew Cooper Try this...

=DATE(VALUE(LEFT([Original Date]@row, 4)), VALUE(MID([Original Date]@row, 6, FIND("-", [Original Date]@row, 6) - 6)), VALUE(MID([Original Date]@row, FIND("-", [Original Date]@row, 6) + 1, FIND(" ", [Original Date]@row) - (FIND("-", [Original Date]@row, 6) + 1))))

Drew Cooper

@Paul Newcome

=DATE(VALUE(LEFT([Last]@row, 4)), VALUE(MID([Last]@row, 6, FIND("-", [Last]@row, 6) - 6)), VALUE(MID([Last]@row, FIND("-", [Last]@row, 6) + 1, FIND("-", [Last]@row) - (FIND(" ", [Last]@row, 6) + 1))))

Comes back as #INVALID VALUE

Paul Newcome

@Drew Cooper You have the values in your last two FIND functions swapped. If you look through the values in each of the FIND functions, it should go

"-"

"-"

" "

"-"

You have

"-"

"-"

"-"

" "

dash dash space dash

vs

dash dash dash space

Drew Cooper

Still not working. Again, trying to get it to show up as: 5-26-2021 (and nothing else).

Thanks for all your help