Converting Year, Month, Day helper columns into a "Date formatted" Date value

.Leon.

Hi all - I am getting really confused by how SmartSheet handles dates.

I have an excel sheet I am importing that loads the date value as a text/number column type - [Imported Date]

I then want to convert this into a Helper column, that displays a date value, formatted as a date - [Helper Date]

I created formulas to split out [YEAR], [MONTH], [DATE] from the text date, but am now having trouble with the formula that would combine all these into one that can be placed in my [Helper Date].

Any help on this would be appreciated, thanks!

Find more posts tagged with

Accepted answers

.Leon.

Figured it out - referenced an old post that uses the VALUE attribute;

https://community.smartsheet.com/discussion/24296/convert-text-date-to-date-2018-04-18

I was able to convert my extracted YEAR, MON, DAY values into a workable DATE formatted column value using the below formula;

=DATE(VALUE(Year@row), VALUE(Month@row), VALUE(Day@row))

All comments

Jason Duryea

If the resulting value is a date, then the column type needs to Date. I would think the Date() function should work if you have Year, Month and Day into separate fields. Or if the Day doesn't matter, "01" will make the day the 1st of the month/year.

https://help.smartsheet.com/function/date

.Leon.

Thanks for the help - if I try this it returns a #INVALID DATA TYPE error message.

=DATE(Year@row, Month@row, Day@row)

For reference,

DATE is formatted as a Date column types

YEAR / MONTH / DAY are formatted as text/number column types

I am creating the helper columns based off a text value date [time_period] ;

YEAR = RIGHT([time_period]@row, 4)

MONTH = LEFT([time_period]@row, (1 + [Helper - Month format]@row))

DAY = MID([time_period]@row, (3 + [Helper - Month format]@row), (1 + [Helper - Day format]@row))

I am using Helper columns in here to indicate if I need to import 1 or 2 digits for day/month depending on the date value - eg 1...9 (1 digit/char) or 10...31 (2 digits/chars) for days, 1..9 (1 digit/char) or 10..12 (2 digits/chars) for months.

Jason Albrecht

Hi Leon,

I know the following suggestion sounds counter-intuitive, but I had a play around with the DATE Function and got the same #INVALID DATA TYPE error message that you did.

So I went back to basics and started with putting numbers in the formula, as per the first DATE Function example.

= DATE ( 2019, 7, 10)

As my Regional Default Format for dates is set to put the day first, it returned the result " 10/07/2019"

I progressively changed each of the sections to refer to the relative cell. and pressed enter before moving onto the next section e.g.

= DATE ( 2019, 7, DAY@row)

= DATE ( 2019, MONTH@row, DAY@row)

= DATE ( YEAR@row, MONTH@row, DAY@row)

I then copied this formula down a few rows and changed the variables.

It seemed to work. I hope this works for you?

.Leon.

Thanks for the suggestion - that didn't work for me either - not even the first attempt just changing one value.

.Leon.

If anyone even knows how to easily transition a text value date into another column formatted as a date, then I could do away with the helper columns entirely?

.Leon.

Figured it out - referenced an old post that uses the VALUE attribute;

https://community.smartsheet.com/discussion/24296/convert-text-date-to-date-2018-04-18

I was able to convert my extracted YEAR, MON, DAY values into a workable DATE formatted column value using the below formula;

=DATE(VALUE(Year@row), VALUE(Month@row), VALUE(Day@row))

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.