Date Formatting from a Formula
Hello,
I'm importing dates from an excel sheet that have a time stamp, and I have a formula that removes the time stamp in Smartsheet. This is fine except the date won't convert to 00/00/00 for that column. Does anyone know if there's a way to covert the format?
Thanks
Best Answer
-
@Marlei Try something like this:
=DATE(VALUE(MID(Timestamp@row, FIND(" ", Timestamp@row) - 4, 4)), VALUE(LEFT(Timestamp@row, FIND("/", Timestamp@row) - 1)), VALUE(MID(Timestamp@row, FIND("/", Timestamp@row) + 1, FIND("/", Timestamp@row, 4) - (FIND("/", Timestamp@row) + 1))))
Answers
-
How is the date displayed currently, and do you want it as mm/dd/yy or dd/mm/yy?
-
@Paul Newcome Hoping to get some help on something very similar.
I am using data shuttle to import data. The data in the excel .xlsx file contains a time stamp.
Example: 3/7/2022 1:00:00 PM PST
After using a complex formula, I was able to extract the date however it comes over as 3/7/2022 and I am not able to find a way to reformat it so that SmartSheet recognizes it as a date for sorting, etc.
I would prefer the format be mm/dd/yy so that it is recognized in a date type column. Any help would be greatly appreciated.
-
@Marlei Try something like this:
=DATE(VALUE(MID(Timestamp@row, FIND(" ", Timestamp@row) - 4, 4)), VALUE(LEFT(Timestamp@row, FIND("/", Timestamp@row) - 1)), VALUE(MID(Timestamp@row, FIND("/", Timestamp@row) + 1, FIND("/", Timestamp@row, 4) - (FIND("/", Timestamp@row) + 1))))
-
It worked! Thank you so much @Paul Newcome
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!