So, using @Bathsheba suggestion, I went around this the long way but it works.
=DATEVALUE(CONCATENATE(MID(A1,1+FIND(" ",A1,FIND(" ",A1)+1),2),MID(RIGHT(A1,LEN(A1)-FIND(" ",A1)),1,3),RIGHT(A1,4)))
MID(A1,1+FIND(" ",A1,FIND(" ",A1)+1),2)-This finds the day in
ddformat from the string in
MID(RIGHT(A1,LEN(A1)-FIND(" ",A1)),1,3)-Takes the first three letters of the month from the string in
RIGHT(A1,4)-Takes the year from the string in
It then takes these values and concatenates them into the format of
dd mmm yyyy. The
DATEVALUE then changes this into the correct format of
dd/mm/yyyy. This works for all dates in you format.
Note: The cell, which this formula lies, should be formatted for short date.
Custom format the cells as
dd/mm/yyyy and use this formula:
or without any additional formatting:
then your dates might need a bit of cleaning :]
=Text( Mid( Trim(Clean(A1)), Find(" ", Trim(Clean(A1)) ), 99) * 1, "dd/mm/yyyy")
I just noticed the vba tag
[a1].NumberFormat = "dd/mm/yyyy" [a1] = Split([a1], , 2)(1) ' "Tuesday August 23, 2016" to "August 23, 2016"
- Excel formula to convert date from dddd mmmm dd, yyyy to dd/mm/yyyy
- Convert this long Excel date formula into VBA
- Excel Formula or VBA to convert year, month data to actual date
- Convert Excel date from date serial number to Hijri date
- Excel Question: I need a date and time formula to convert between time zones
- Excel VBA : Convert date format from YYYY-MM-DD-HH.MM.SS.Milli to DD/MM/YYYY HH:MM:SS
- convert date in excel from dd-mm-yyyy to dd/mm/yyyy format and to check whether a date is valid or not
- Excel Date Conversion from yyyymmdd to mm/dd/yyyy
- Excel VBA: Convert a date string to a Unix timestamp
- Excel vba get month from Date object
- Extract URL From Excel Hyperlink Formula
- Writing a date from Excel VBA to a worksheet gives the wrong value
- How to convert the actual excel formula (not the result) to a text string
- Convert String to Excel Date and Time, with specific string format
- How do I get the cell value from a formula in Excel using VBA?
- Extract Year from Date Using VBA in Excel
- Extract formula from Excel Data Table (What-If Analysis)
- How to convert and compare a date string to a date in Excel
- Excel - Get Week Day Start Date as formula
- Is there an excel formula like INDIRECT to get a dependent drop down from another workbook?
- Get email from Outlook to Excel specified by received date
- Sending email from excel automatically based on date
- Excel VBA removes equal sign from formula
- Excel using INDEX to get row from named range causes formula error
- Convert code from Excel Macro to PowerShell
- VBA Excel add-in: code to change format of column from general to custom date
- How do I convert a number like 20120103 into a date that Excel can recognize?
- Insert a function / formula in Excel from R and VBA & Apply a VBA macro from R
- How to convert a range of cells from Dec to Hex in Excel
- how to get real formula from EXCEL formula
More Query from same tag
- Renaming Files with Excel VBA
- VBA WebScraping How To select value from a drop dropdown box
- How to pass LastRow to a function
- Issues with comparing two workbooks
- Assigning macros to a combobox
- Paste vs PasteSpecial
- want to select Cell that has specific condition
- Store all dates between 2 dates in vba into an array
- Keyboard shortcut inside a module to call itself
- Zero value of my variables are not displaying in the Active sheet I assigned
- OnCurrent event firing when moving from first record to the last but not from last to the first
- Excel sheet name issues for ADODB queries
- Stopping SQL query date/time format from carrying over to different sheets
- Macro incorrectly deleting table lines
- How to edit column width of an Excel report which was created by an Access query?
- Return type in findById method, control could not be found by id
- How to save all worksheets in Excel workbook as one text file?
- How to pass an Excel worksheet name to an Access subroutine?
- Access 2007, VBA: Compile error: expected end of statement
- Convert an array of string into new string in VBA
- Creating Multiple Charts using VBA
- assign IF(AND formula to the cell in VBA
- Setting Object Library References Dynamically possible?
- VBA Code to Populate Balance Sheet from Trial Balance Data
- Executing VBAscript from Python
- Getting reccuring appointments VBA
- download certain tables from mysql server to particular location as csv files
- VBA Data missing on the copied Pivot Table
- Is it possible to determine if two objects both implement a common interface which is not specified at compile time?
- Identify row number where preceding cell value in column differ only in visible range