UDF Function

check your regional settings first.  In some cases you may be copying values using M-D-Y format and pasting them as D-M-Y

So, first you should check to see what kind of format is the copied data first and then paste it

please find attached

Public Function ExtractDate(fromThis As Range) As String
   
     Dim retVal As String
     Dim ltr As String, i As Integer, datecheck As Boolean
    
     retVal = ""
     ExtractDate = ""
     datecheck = False
    
     On Error GoTo last

     If fromThis.Value Like "*/*/*" Then
         datecheck = True
     ElseIf fromThis.Value Like "*-*-*" Then
         datecheck = True
     End If
    
     For i = 1 To Len(fromThis)
         ltr = Mid(fromThis, i, 1)
         If IsNumeric(ltr) Then
             retVal = retVal & ltr
         ElseIf ltr = "/" And datecheck Then
             retVal = retVal & ltr
         ElseIf ltr = "-" And datecheck Then
             retVal = retVal & "-"
         End If
     Next i
     ExtractDate = Format(retVal, "mm/dd/yyyy")
last:
 End Function

Select allOpen in new window

EE.xlsb

My PC setting is M/D/YYYY...and i copying my ledger data from website which is i know D/M/YYYY format.

See Attached

Thanks
Functions-For-Format.xlsx

oppps sorry comment posted and seen Professors Post...let me check

Thanks

You only need this....

Function MyDate(rng As Range) As Date
MyDate = rng.Value
End Function

Select allOpen in new window

Then use it like =MyDate(B2) and then apply custom number formatting as dd-mmm-yy so that those dates will be treated as real dates not the dates as Text.
Date.xlsm

As per your previous question, some of the values in the sample are dates and some are text that look like dates.

Did Text to Columns not do what you required when specifying that the values were in DMY format?

If not how about these formulas instead?
1) If you want the result to stay as a date value:  =IF(ISNUMBER(B3),B3,DATE(RIGHT(B3,4),MID(B3,4,2),LEFT(B3,2)))
Change format of cell as required, use custom format of "dd-mmm-yy" to get same appearance.

2) If you want to convert to text:
=TEXT(IF(ISNUMBER(B2),B2,DATE(RIGHT(B2,4),MID(B2,4,2),LEFT(B2,2))),"dd-mmm-yy")

Converting to text will render the cells unuseable for onward calculations unless you allow for the text in those onward calculations eg DATEVALUE function will convert what looks like a date to a date serial number.

Thanks
Rob

If you know the data being copied is in d/m/y format, when pasting ensure you paste as text, all values will then be exactly as displayed on the website and the above formulas will work. If they are definitely text, you will only need:

=DATE(RIGHT(B3,4),MID(B3,4,2),LEFT(B3,2))

or

=TEXT(DATE(RIGHT(B3,4),MID(B3,4,2),LEFT(B3,2)),"dd-mmm-yy")

Alternatively, look at the options in your browser settings to read the dates in the correct format.

When I first started on EE the dates were reading wrong for me, EE being US based they were reading in mm-dd-yy format. I changed my browser settings and they changed to correct format.

Thanks
Rob

Had used all 3 Solutions but result is not proper.Thank You

Do you download from the web or copy and paste from the screen?

If you download, you may be able to download as a txt or csv and then import into excel rather than pasting in.

If you don't have the option to download copy and paste into notepad and save as a txt and then import.

I suspect Excel is trying to be too clever for its own good and is converting the values as they are pasted because it thinks it recognises the values as dates and converts based on your regional settings. For example, pasting 01/04/2016 into your screen will automatically convert to 4 Jan 16 with your settings but as you know it is in d/m/y format it should be 1 Apr 16; pasting the same into my pc gives 1 Apr 16 because my PC has UK regional settings.

Another example, 22/04/16, your system won't recognise it because there is no month 22 so it pastes it as text.

If you force the paste to text or import from a text file and tell the import wizard what to do (same steps as the text to Column wizard) it will work correctly.

Are you able to share the web address that you are copying from?

Do you download from the web or copy and paste from the screen?

Copy from the screen ...That site  is very pathetic ...there is option  import to .csv but it never works....even in my home pc that ledger screen i cant open ...only from office PC i can see screens ...my home PC is bit advanced then office PC. might be that is the case.


thanks

it requires logins and if only mine data then i dont have problem to share but there is  my client data too ..Basically i am sub Broker and trying to maintain client ledger in excel.

Thanks

OK, have you tried adjusting browser settings?

That would affect all web pages though. If web pages are setup correctly, it shouldn't matter, the code behind the page should recognise dates as dates.

How about trying a data connection directly to the web page?

How about trying a data connection directly to the web page?

Do you Data Connection Via Excel?

If yes then not feasible as too many clients codes and too many financial year which i have to select in Web page.

or i misunderstood what you said.

Thanks

@@Neeraj

Yes it is working.

Thanks

Thanks

You're welcome. Glad we all could offer you some help.

OK... some points for assistance would have been appreciated!!

@itjockey

i am not trying to impugn anyone's work here but the UDF you accepted does not generate what you described.

look at your B5 cell, it should be 06-Sep-16  while the UDF generates 9-Jun-16


the solution you are looking for can be archived using built in formulas.

=TEXT(DATE(RIGHT(LEFT(TEXT(B2,"DD/MM/YYYY")&"QQ",LEN(TEXT(B2,"DD/MM/YYYY")&"QQ")-2),4),MID(TEXT(B2,"DD/MM/YYYY")&"QQ",4,2),LEFT(TEXT(B2,"DD/MM/YYYY")&"QQ",2)),"DD-MMM-YY")

please see attached.
EE.xlsx

@ Proffesor

9/6/2016 .....D/M/YYYY i.e. 9 - Jun - 2016

See Here Which i Highlighted

Thanks

@ProfJJ - looks like you have fallen foul of the same confusion I have been having with this due to mixed Region settings.

The screen shot in that comment shows B5 as 9/6/2016 but when I open the file it converts to 9 Jun 2016. I guess you have US regional settings and it incorrectly converts that date to 6 Sept 2016, hence my reverse logic in my suggestion and I believe Neeraj has the same reverse logic.

Initial values (after pasting) are:
Text = Not recognised therefore showing correctly
Numeric = recognised and converted but incorrectly

@itjockey

thanks for clarification, i misunderstood your question based on the screenshots you posted earlier.

in that case Neeraj's UDF is perfect, as well as Rob's formula.

Yes Rob,

thanks for the elaboration.  your latest formula works for OP , so as neeraj's UDF.

please do not allocate any points to me.

Thank You Mr. Wolfe