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
686 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
458 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,578 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,130 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
658 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,810 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,152 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
1,071 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,975 views
0 votes
1 answer

In MS Excel what formula can be used to calculate growth when CAGR for the period is already given?

If those are true CAGR's, then just ...READ MORE

answered Nov 20, 2022 in Others by narikkadan
• 63,600 points
1,014 views
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
960 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