Date Format
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
this easier.
Best Answers
-
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))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Nope - and that solved the issue.
Thanks Paul!!!
Answers
-
Is it going into a text/number column?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
No it is going into a date column.
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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"
-
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))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks Paul. I attempted this and the new column is now returning an #INVALID VALUE Error. See attachments
.
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks Paul!! You are the MAN!!!!!!!!!!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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!!
-
@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))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
=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
-
@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
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Still not working. Again, trying to get it to show up as: 5-26-2021 (and nothing else).
Thanks for all your help
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!