+ Reply to Thread
Results 1 to 10 of 10

Creating a user defined function based on a complex worksheet formula

  1. 11-24-2014, 04:36 AM #1
    Atul Maskara
    Atul Maskara is offline
    Registered User
    Join Date
    07-18-2013
    Location
    Kolkata
    MS-Off Ver
    Excel 2010
    Posts
    21

    Creating a user defined function based on a complex worksheet formula

    Hi,

    I have a formula written in excel for which I would like to have a udf, because it is a large formula and I would like lot of people to use it.. so instead of having a large formula being written every time, if there is udf that can be written would make the life easy.

    The formula that I would like to convert in udf is:

    =DATEVALUE(MID(R2,FIND("/",R2)+1,FIND("/",R2,FIND("/",R2)+1)-FIND("/",R2)-1)&"/"&LEFT(R2,FIND("/",R2)-1)&"/"&MID(R2,FIND("/",R2,FIND("/",R2)+1)+1,4))

    What this formula does is it converts "26/08/2014 12:00:00 AM" in "8/26/2014" format.

    So if there is a short udf which can take care of this formula would be really helpful.

    Thanks in advance,

    Regards,
    Reply With QuoteRegister To Reply
  2. 11-24-2014, 05:01 AM #2
    michson
    michson is offline
    Registered User
    Join Date
    09-10-2014
    Location
    Matrix
    MS-Off Ver
    2010
    Posts
    70

    Re: Creating a user defined function based on a complex worksheet formula

    Use this UDF:
    Please Login or Register  to view this content.
    Last edited by michson; 11-24-2014 at 05:18 AM.
    (*) Reputation points appreciated.
    excelbs.tk
    Reply With QuoteRegister To Reply
  3. 11-24-2014, 05:02 AM #3
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Creating a user defined function based on a complex worksheet formula

    =text(left(text(a1,"m/dd/yyyy"),10),"m/dd/yyyy")


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved

    If the suggestion helps you, then Click *below to Add Reputation
    Reply With QuoteRegister To Reply
  4. 11-24-2014, 05:03 AM #4
    tehneXus
    tehneXus is offline
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Creating a user defined function based on a complex worksheet formula

    Hi,

    there is more than one solution for this:

    1. Change the formatting of the cell your converted date should be, into custom "MM/DD/YYYY" and use formula "=DATEVALUE(R2)", date value is returned.

    2. Use an UDF to convert the target cell's formatting and fill it as date value:
    Please Login or Register  to view this content.
    3. Use an UDF to return a string value: 
    Please Login or Register  to view this content.
    Please use [CODE]-TAGS
    When your problem is solved mark the thread SOLVED
    If an answer has helped you please click to give reputation
    Read the FORUM RULES
    Reply With QuoteRegister To Reply
  5. 11-24-2014, 05:03 AM #5
    karedog
    karedog is offline
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Creating a user defined function based on a complex worksheet formula

    Hi,

    Could be this one :

    Please Login or Register  to view this content.
    Regards
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important
    Reply With QuoteRegister To Reply
  6. 11-24-2014, 08:24 AM #6
    Atul Maskara
    Atul Maskara is offline
    Registered User
    Join Date
    07-18-2013
    Location
    Kolkata
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Creating a user defined function based on a complex worksheet formula

    Hi Michson,

    Thanks for your response.. your UDF is working fine.. except for some instances where: 1/09/2014 12:00:00 AM is returned as 09/1//2014.. can there be any fix for this?

    Thanks a lot
    Reply With QuoteRegister To Reply
  7. 11-24-2014, 08:36 AM #7
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Creating a user defined function based on a complex worksheet formula

    Couldn't you simplify the formula using INT and TEXT, or INT and a custom format?
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  8. 11-24-2014, 09:03 AM #8
    Atul Maskara
    Atul Maskara is offline
    Registered User
    Join Date
    07-18-2013
    Location
    Kolkata
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Creating a user defined function based on a complex worksheet formula

    is that possible? I did not try it out.. if you can post an example of how to work it out with INT and TEXT and Custom Format..

    I have attached the sample file below:
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 11-24-2014, 09:11 AM #9
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Creating a user defined function based on a complex worksheet formula

    Works for me in your file.

    Put this in A2, copy down and then apply the custom format mm/dd/yyyy.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  10. 11-24-2014, 09:36 AM #10
    Atul Maskara
    Atul Maskara is offline
    Registered User
    Join Date
    07-18-2013
    Location
    Kolkata
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Creating a user defined function based on a complex worksheet formula

    Can you please send the file.. it's not working for me.. I have attached the file
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Complex User-Defined Function to calculate CFs and discount them
    By arthuro in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-22-2014, 08:23 AM
  2. Creating a User-Defined Function
    By Excel_vba in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-19-2014, 11:10 PM
  3. Excel 2007 : Creating user defined function
    By cassieyi in forum Excel General
    Replies: 1
    Last Post: 10-02-2011, 04:58 PM
  4. Creating my own user defined function help statements
    By Craig in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-22-2006, 12:55 PM
  5. creating help info for user defined function
    By Joe Carroll in forum Excel General
    Replies: 3
    Last Post: 06-04-2005, 07:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1