How to extract time from Date created auto column?
Hi all, I want to extract only time from Date created auto column (in format HH:mm:ss)
I've tried VALUE(RIGHT(Created@row, 8)), but it seems i can only take two first numbers and ":" is blocking the formula, and i get unparsable if i go any higher than VALUE(RIGHT(Created@row, 2)).
What would be the correct approach to achieve this?
Answers
-
Hi @Colemont ,
Unfortunately, there is no easy workaround for what you are trying to achieve. Although there is the DATEONLY formula to extract the date from your auto-generated column, there is none to extract the time.
You'll need to insert a helper column in your current working sheet and a separate sheet for VLOOKUP (you can also use INDEX/MATCH formula if you dig in further).
So use the automation template to copy/move rows to another sheet, in this case your VLOOKUP sheet. This would be your date/time column (that was auto-created) that you copy over to the VLOOKUP sheet.
Now, using either the VLOOKUP or the INDEX/MATCH formula you can bring it back to the main sheet in your helper column (the one you create for bringing the time value).
I know this is a longer method but hopefully this helps. Please submit your excellent idea to have a calculation to separate time from a date column in Smartsheet portal.
Cheers!
Ipshita
Ipshita Mukherjee
-
Hi and thank you for your answer. Unfortunately, we are not able to use helper columns anymore on our data sheets. We have 50+ columns already with 20 Helper columns of these. Many sheets are already reaching cell limits,. It would create even more frustration for other index/vlookups to etc. There are limits in these, so i'm looking for another solution. Thank you! :)
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 352 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 135 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!