UDF Function

Hi Expert,

Will Anyone help me with UDF Function that convert text date to date Format. As in attached i have dates copy pasted from web but dates behave weirdly. if i use =TEXT(Cell,"DD-MMM-YY") some shows perfect date and some are return with error so any one help me VBA function?
My dates are in this format 1/4/2016 i.e. 1 - April - 2016...i need Function like =Mydate("/",D,M,Y)......where "/" is separator. sometimes separator format may be change in web like 01-04-2016 so need separator in argument. Some times date like this 1-4-2016 which is 4 Jan 2016 so that is why D,M,Y argument is also required.

Thanks
Date.xlsx
LVL 8
Naresh PatelTraderAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Does this work for you?

Function MyDate(rng As Range) As Date
If WorksheetFunction.IsText(rng) Then
   MyDate = DateSerial(Year(rng.Value), Month(rng.Value), Day(rng.Value))
Else
   MyDate = DateSerial(Year(rng.Value), Day(rng.Value), Month(rng.Value))
End If
End Function

Open in new window

0
 
xtermieCommented:
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
0
 
ProfessorJimJamCommented:
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

Open in new window

EE.xlsb
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Naresh PatelTraderAuthor Commented:
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
0
 
Naresh PatelTraderAuthor Commented:
oppps sorry comment posted and seen Professors Post...let me check

Thanks
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You only need this....

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

Open 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
0
 
Rob HensonFinance AnalystCommented:
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
0
 
Rob HensonFinance AnalystCommented:
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
0
 
Naresh PatelTraderAuthor Commented:
Had used all 3 Solutions but result is not proper.ProfessorNeerajRobThank You
0
 
Rob HensonFinance AnalystCommented:
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.
0
 
Rob HensonFinance AnalystCommented:
Are you able to share the web address that you are copying from?
0
 
Naresh PatelTraderAuthor Commented:
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
0
 
Naresh PatelTraderAuthor Commented:
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
0
 
Rob HensonFinance AnalystCommented:
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?
0
 
Naresh PatelTraderAuthor Commented:
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
0
 
Naresh PatelTraderAuthor Commented:
@@Neeraj

Yes it is working.

Thanks
0
 
Naresh PatelTraderAuthor Commented:
Thanks
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad we all could offer you some help.
0
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
OK, maybe data connection not viable with number of inputs required.

Based on Neeraj's function suggestion, reverse the logic of my earlier formula suggestion:

=IF(ISNUMBER(B2),DATE(RIGHT(TEXT(B2,"dd/mm/yyyy"),4),LEFT(TEXT(B2,"dd/mm/yyyy"),2),MID(TEXT(B2,"dd/mm/yyyy"),4,2)),DATE(RIGHT(B2,4),MID(B2,4,2),LEFT(B2,2)))

As mentioned earlier, after pasting the values your system will try to convert the pasted value based on a mm/dd/yy format so doesn't recognise an entry such as 22/04/2016 and pastes it as text. If it does recognise the value as a date it will convert it but will do it the wrong way, eg 01/04/2016 gets converted to 4 Jan.

So...

If you know these things are going to happen you can apply the following logic:
- Text Values occur when it doesn't recognise the date so will be text in dd/mm/yy format
- numeric values occur when date is recognised but will be wrong way round

Therefore, the correct values can be extracted directly from text values dd = left 2 characters, month = middle 2 characters, year = right 4 characters.

With numeric values excel will see the value behind the scenes as a serial number, for example 1 Apr 16 is serial number 42461 (number of days since start of excel time back in 01/01/1900) so splitting to leftt 2, middle 2, right 4 won't work unless you convert it to text first.

Hope that explains the above formula!!

Thanks
Rob H
0
 
Rob HensonFinance AnalystCommented:
OK... some points for assistance would have been appreciated!!
1
 
ProfessorJimJamCommented:
@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
0
 
Naresh PatelTraderAuthor Commented:
@ Proffesor

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

See Here Which i Highlighted

Thanks
0
 
Rob HensonFinance AnalystCommented:
@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
1
 
ProfessorJimJamCommented:
@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.
0
 
ProfessorJimJamCommented:
Yes Rob,

thanks for the elaboration.  your latest formula works for OP , so as neeraj's UDF.
1
 
ProfessorJimJamCommented:
please do not allocate any points to me.
0
 
Naresh PatelTraderAuthor Commented:
Thank You Mr. Wolfe
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.