Solved

UDF Function

Posted on 2016-08-24
28
71 Views
Last Modified: 2016-08-25
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
0
Comment
Question by:itjockey
  • 10
  • 8
  • 5
  • +2
28 Comments
 
LVL 17

Expert Comment

by:xtermie
ID: 41768743
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
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41768751
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
 
LVL 8

Author Comment

by:itjockey
ID: 41768754
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
 
LVL 8

Author Comment

by:itjockey
ID: 41768757
oppps sorry comment posted and seen Professors Post...let me check

Thanks
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41768759
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41768767
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41768774
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
 
LVL 8

Author Comment

by:itjockey
ID: 41768789
Had used all 3 Solutions but result is not proper.ProfessorNeerajRobThank You
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41768790
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41768793
Are you able to share the web address that you are copying from?
0
 
LVL 8

Author Comment

by:itjockey
ID: 41768799
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
 
LVL 8

Author Comment

by:itjockey
ID: 41768806
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41768810
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 300 total points
ID: 41768821
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
 
LVL 8

Author Comment

by:itjockey
ID: 41768823
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
 
LVL 8

Author Comment

by:itjockey
ID: 41768831
@@Neeraj

Yes it is working.

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 41768833
Thanks
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41768836
You're welcome. Glad we all could offer you some help.
0
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 200 total points
ID: 41768840
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41768843
OK... some points for assistance would have been appreciated!!
1
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41768886
@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
 
LVL 8

Author Comment

by:itjockey
ID: 41768963
@ Proffesor

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

See Here Which i Highlighted

Thanks
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41769004
@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
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41769005
@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
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41769007
Yes Rob,

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

Expert Comment

by:ProfessorJimJam
ID: 41769008
please do not allocate any points to me.
0
 
LVL 8

Author Comment

by:itjockey
ID: 41770614
Thank You Mr. Wolfe
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now