Month and Year from a Date - #INVALID DATA TYPE
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
-
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
-
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
-
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
-
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))))
-
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
-
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))))
-
Outstanding Paul,
Thank you very much!
Greetings from Poland, Romano!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!