Date and Time Separator Formula

Hi Community,

I'm looking for assistance with creating formulas in Smartsheet to separate a combined DateTime value into two distinct columns: one for the date and one for the time.

"Date" Column: Contains both date and time values
"Date Only" Column: Should display only the date
"Time Only" Column: Should display only the time

Formulas I've attempted for "Date Only" :

=LEFT(Date@row , FIND("-", Date@row , FIND("-", Date@row ) + 1))

Brings back "Year and Month" only

=LEFT([Date]@row , FIND("-", [Date]@row , FIND("-", [Date]@row ) + 1, FIND("-", [Date]@row ) + 1))) #UNPARSEABLE

=LEFT([Date]@row , FIND("-", [Date]@row , FIND("-", [Date]@row ) + 1, FIND("-", [Date]@row ) + 2))) #UNPARSEABLE

Formulas I've attempted for "Time Only" :

=RIGHT(Date@row , FIND(":", Date@row , FIND(":", Date@row ) + 1))

Brings back "Date and Time"

Any guidance on the appropriate formulas to achieve this would be greatly appreciated. Thank you!

-AD😁

Find more posts tagged with

Sheets

Formulas

Automations

Accepted answers

Paul Newcome

Try these…

Date Only:

=DATE(VALUE(LEFT(Date@row, 4)), VALUE(MID(Date@row, 6, 2)), VALUE(MID(Date@row, 9, 2)))

Time Only:

=RIGHT(Date@row, 8)

All comments

Paul Newcome

Try these…

Date Only:

=DATE(VALUE(LEFT(Date@row, 4)), VALUE(MID(Date@row, 6, 2)), VALUE(MID(Date@row, 9, 2)))

Time Only:

=RIGHT(Date@row, 8)

Adarian Searcy

Hi @Paul Newcome,

Thank you for the assistance! The formula for the "Time Only" column is working perfectly. However, the "Date Only" column is returning #INVALID COLUMN VALUE.

Any suggestions on how to resolve this would be greatly appreciated.

Paul Newcome

Make sure you have the column properties set so that it is a date type column.

Adarian Searcy

😖, thank you!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.