Month and Year from a Date - #INVALID DATA TYPE

Romano el Polako
Romano el Polako ✭✭✭
edited 11/28/23 in Formulas and Functions

Hello Super Smart Guys!

I am struggling with month and year extraction from a date. The date is extracted automatically as raw data from salesforce, please refer screen:

Based on answer from this community, I was able to extract Date Only and remove hour, formula used:

=LEFT([Date/Time Opened]@row; FIND(" "; [Date/Time Opened]@row))

In next two columns, I have added following formulas:

=MONTH([Date Only]@row)

=YEAR([Date Only]@row)

Unfortunately, I receive #INVALID DATA TYPE where I should receive Month and Year.

When it comes to column types:

I have tried various combinations of types, every time with the same results.

I read somewhere that this issue can be triggered by blank row, hence I added a workflow to ensure these rows are moved to another sheet, see:

Having a month and year is pretty critical for further reporting, hence thank you in advance for support and sorry in case the question is a duplicate of legacy content.

Thanks,

Romano

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I think I just missed a "+1" try this adjustment:

    =DATE(VALUE(MID([Date/Time Opened]@row; FIND("/"; [Date/Time Opened]@row; FIND("/"; [Date/Time Opened]@row) + 1) + 1; 4)); VALUE(LEFT([Date/Time Opened]@row; FIND("/"; [Date/Time Opened]@row) - 1)); VALUE(MID([Date/Time Opened]@row; FIND("/"; [Date/Time Opened]@row) + 1; FIND("/"; [Date/Time Opened]@row; FIND("/"; [Date/Time Opened]@row) + 1) - (FIND("/"; [Date/Time Opened]@row) + 1))))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!