+ Reply to Thread
Results 1 to 5 of 5

Macro to change date format and then run function based on greater than or equal to

  1. #1
    Forum Contributor
    Join Date
    01-21-2010
    Location
    Glasgow
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    105

    Macro to change date format and then run function based on greater than or equal to

    Hi there

    What I want is for the date format to be changed from dd/mm/yy 00:00:00 to dd/mm/yy which I can do with formula
    Please Login or Register  to view this content.
    but I'm not sure how to do this in code form column H

    I then need to take the new date format and if that is greater than or equal to 20/01/2010 then run the formula
    Please Login or Register  to view this content.
    All in a macro.

    I was trying advanced filter but I am not sure how to do this.

    This code works for the full column but does not only run weekday formula for anything after 20/01/2010 plus is in wrong date format so cannot do analysis on it after.

    Please Login or Register  to view this content.
    All help gratefull recieved.


    Libby

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Macro to change date format and then run function based on greater than or equal

    Hello Libby,

    It looks like you need a User Defined Function or UDF for short. A UDF can be used on a Worksheet like a formula. If I understand what you want to do correctly then this UDF should do it. If not, let me know and we'll work on it. The result is returned to the cell that called the UDF.
    Please Login or Register  to view this content.
    UDF Example
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Contributor
    Join Date
    01-21-2010
    Location
    Glasgow
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    105

    Re: Macro to change date format and then run function based on greater than or equal

    Hi Leith,

    Thanks for this. I was trying to avoid putting any formula in the sheet as the sheet has around 30 thousand rows and it adds to the memory.

    Plus it won't let me put
    Please Login or Register  to view this content.
    in it says name not valid.

    Is there any way we can put it in the code without having any formula in the sheet. If not what do I need to do to allow this to work.

    Thank you and apologies for the delay in getting back to you.

    Libby

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Macro to change date format and then run function based on greater than or equal

    Hello Libby,

    I have rewritten the macro. You can either run this using ALT+F8 (Macro Dialog) or by attaching it to a button on the sheet. It assumes the data is on the "Sheet1". It compares the values in column "H" against the deadline in the macro. Once all the dates have been checked, the new dates are copied over to "Sheet2" starting in cell "A1".
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    01-21-2010
    Location
    Glasgow
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    105

    Re: Macro to change date format and then run function based on greater than or equal

    Hi there,

    Thank you for this. Unfortunately this coopies and pastes everything from 20/01/2010 into A1 of Sheet 2 but that is it. It is not removing the time facttor or adding my formula.

    I suspect it is something I have done. I would like it to return the item in column I if poss too.

    I'm not sure if it is me but I suspect it is.

    Thank you
    Libby

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1