• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 110
  • Last Modified:

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
0
Naresh Patel
Asked:
Naresh Patel
  • 10
  • 8
  • 5
  • +2
2 Solutions
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 HensonIT & Database AssistantCommented:
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 HensonIT & Database AssistantCommented:
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 HensonIT & Database AssistantCommented:
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 HensonIT & Database AssistantCommented:
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 HensonIT & Database AssistantCommented:
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
 
Subodh Tiwari (Neeraj)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
 
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 HensonIT & Database AssistantCommented:
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 HensonIT & Database AssistantCommented:
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 HensonIT & Database AssistantCommented:
@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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 10
  • 8
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now