Month and Year from a Date - #INVALID DATA TYPE

Romano el Polako

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

Paul Newcome

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

Paul Newcome

The issue is that your LEFT function outputs a text string instead of a date value (which would need to be put into a date type column).

Will the months and days in your date/time string always be two digits?

12/16/2023

01/05/2023

Romano el Polako

Hey Paul,

Thank you for an update!

Date format from Date/Time Opened is in 1 or 2 digit format, see examples:

12/16/2022 5:48 AM

12/20/2022 4:17 AM

1/10/2023 2:30 AM

1/10/2023 7:31 AM

I had the feeling that the LEFT formula is messing it up, is there any other way to extract MONTH and YEAR straight from this input data?

Thanks,

Roman

Paul Newcome

You will need to use something like this:

=DATE(VALUE(MID([Date/Time Opened]@row; FIND("/"; [Date/Time Opened]@row; FIND("/"; [Date/Time Opened]@row) + 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))))

Romano el Polako

Hey Paul, sorry for late response.

I tried your formula, but does not seem to work, I tried in two columns:

Date - Text, where column type is text/number and

Date - Date, (...) is date

Do you have any other idea, hidden somewhere in your magic formula sleeve?

Romano

Paul Newcome

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

Romano el Polako

Outstanding Paul,

Thank you very much!

Greetings from Poland, Romano!

Paul Newcome

Happy to help. 👍️