What is the shortest formula in excel to covert text date format e g 17 Jan 2023 into regular date format like 1 17 2023

0 votes

I have sales data with text indicating the transaction date, such as "17 January 2023." I wish to use the TEXT(A1,"MMM") or MONTH(A1) functions to extract the month name, however, these two functions don't work with text date formats.

I tried numerous formulas to change the text date format to the standard date format, but I was unable to find the optimal little function or formula.

Later, I attempted the method below, but it is too lengthy and intricate to utilize.

=DATE(RIGHT(A1,4),MATCH(MID(A1,1,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0)-1+1,DAY(LEFT(A1,FIND(",",A1)-1)))

My question is if anybody knows are the shortest way to do the same.

Jan 30, 2023 in Others by Kithuzzz
• 38,000 points
589 views

1 answer to this question.

0 votes

The SUBSTITUTE() method can be used to eliminate the, that exists in your text, and then nesting this in the VALUE() function should yield an Excel-recognised date, i.e., 17 January 2023. However, you must render the date format manually if you are using an English-language locale.

=VALUE(SUBSTITUTE(A1,","," "))
answered Jan 30, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
0 answers

What is the formula to keep first two words in a cell over excel?

I want to maintain the first two ...READ MORE

Nov 2, 2022 in Others by Kithuzzz
• 38,000 points
397 views
0 votes
1 answer

What is the better API to Reading Excel sheets in java - JXL or Apache POI

Here are the things where both APIs ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,600 points
3,434 views
0 votes
1 answer

Excel VBA : HOW TO PRINT THE TEXT IN A CELL (like Wrap Text)

Use a LineFeed character to create a ...READ MORE

answered Oct 27, 2022 in Others by narikkadan
• 63,600 points
1,047 views
0 votes
0 answers

Converting feet & inches (e.g., "5 ft 1 in") to decimal feet using VBA or Excel

I have a database with a few ...READ MORE

Nov 2, 2022 in Others by Kithuzzz
• 38,000 points
599 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
4,153 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
2,021 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,690 points
997 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
3,734 views
0 votes
1 answer
0 votes
1 answer

How to divide data in excel into 4 columns whose sum is almost equal to 1/4 of the sum of all values/

5049 is the sum of all numbers, ...READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,600 points
826 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP