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

Options

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!

Best Answer

Answers

  • Jason Duryea
    Jason Duryea ✭✭✭✭✭✭
    Options

    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.
    .Leon. ✭✭
    edited 09/15/21
    Options

    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
    Jason Albrecht ✭✭✭✭✭✭
    Options

    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?

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • .Leon.
    .Leon. ✭✭
    Options

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

  • .Leon.
    .Leon. ✭✭
    Options

    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.
    .Leon. ✭✭
    Answer ✓
    Options

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



    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))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!