Standardising imported date formats

Hi,

I hope you are all well, and someone may be able to assist:

On a sheet I am working on, a 3rd party app runs an automation that imports a date into a specified column in smartsheet.

The date when imported arrives in the following format: "2023-01-04 12:26:14".

There is no way to change the format which this date is imported in.

I would like this to display in a standard format, excluding the time, i.e "04/01/2023".

If I change the date format for the column to "Regional (Default Format)" then the date changes to the correct format, but only if I double click on the cell and then click out of it.

I am happy to use a helper column if required.

Any advice would be welcomed!

Thank you, Glen.

Find more posts tagged with

Formulas

Accepted answers

Paul H

@Glen Urquhart

Try this formula, with the imported close date column set as text/number, Closed Date column set as Date

=DATE(VALUE(LEFT([Imported close date]@row, 4)), VALUE(MID([Imported close date]@row, 6, 2)), VALUE(MID([Imported close date]@row, 9, 2)))

Andrée Starå

@Glen Urquhart

Happy to help!

I saw that Paul had answered already!

Let me know if I can help with anything else!

Best,

Andrée

All comments

Hollie Green

I would use a helper column and use the formula

=Dateonly([imported column]@row)

Glen Urquhart

Hi Hollie, thanks for your message.

Unfortunatly this returns an invalid data type. :(

Cheers, Glen.

Andrée Starå

Hi @Glen Urquhart

I hope you're well and safe!

To add to Hollie's excellent advice/answer.

Ensure that the column is set to the Date type.

Did that work/help?

I hope that helps!

Have a fantastic week & Happy New Year!

Best,

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

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

Glen Urquhart

Hi Andrée,

Thanks for the note.

Both Columns are in date type colums, however the result is still Invalid.

Example screenshot below!

Cheers, Glen.

Andrée Starå

@Glen Urquhart

Happy to help!

Ah, I see. I though you used the Created System Column. I'll get back to you with the solution.

Paul H

@Glen Urquhart

Try this formula, with the imported close date column set as text/number, Closed Date column set as Date

=DATE(VALUE(LEFT([Imported close date]@row, 4)), VALUE(MID([Imported close date]@row, 6, 2)), VALUE(MID([Imported close date]@row, 9, 2)))

Andrée Starå

@Glen Urquhart

Happy to help!

I saw that Paul had answered already!

Let me know if I can help with anything else!

Best,

Andrée

Glen Urquhart

Hi @Hollie Green, @Andrée Starå & @Paul H,

Thank you all for your input and help - This solution has resolved the problem.

Thank you once again.

Cheers, One happy Smartsheet-er.

Andrée Starå

@Glen Urquhart

You're more than welcome!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.