+ Reply to Thread
Results 1 to 6 of 6

VBA : Date Format()

  1. #1
    Registered User
    Join Date
    04-15-2019
    Location
    italy
    MS-Off Ver
    office 365
    Posts
    3

    VBA : Date Format()

    Dears,

    i hope that someone could help me to understand better why the following code does not function well.

    Please Login or Register  to view this content.
    but if you the contets of the variable

    VBA.PNG

    Did I do something wrong? What am I missing?

    Thanks a lot.
    Rossofoco
    Last edited by rossofoco; 04-15-2019 at 07:52 AM.

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: VBA : Date Format()

    Hi there,

    See if the following version of your code does what you need:

    Please Login or Register  to view this content.


    The real problem in your code is that in the following statement:

    Please Login or Register  to view this content.
    you are attempting to assign a string expression to the variable "filtrodata2" which is declared as a Date variable - the situation is not helped by the fact that the "open-ended" On Error Resume Next statement prevents the "Type Mismatch" error message from giving you an indication as to where the problem is occurring.



    Just for information, the statement:

    Please Login or Register  to view this content.
    declares xWs as a VARIANT, and xlWs as a worksheet - the same applies to your other Dim statements, i.e. the variables whose types are not explicitly declared are implicitly declared as being of type Variant by default.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M

  3. #3
    Registered User
    Join Date
    04-15-2019
    Location
    italy
    MS-Off Ver
    office 365
    Posts
    3

    Re: VBA : Date Format()

    Hi Greg,

    thanks a lot for the tips.

    I've changed "filtrodata2" to string but now the result is the following:

    Attachment 620259


    Actually what i would as output in filtrodata2 is the date formatted from 03/06/2019 to 03.Jun.2019.

    This is only a part of my code. The contents of filtrodata2 will be used to filter a file.

    ActiveSheet.Range("A3:W3").AutoFilter Field:=18, Criteria1:=filtrodata2

    Attachment 620266

    where gg = dd
    mmm = mmm
    aaaa = yyyy

    Thanks
    Amedeo
    Last edited by rossofoco; 04-16-2019 at 05:51 AM.

  4. #4
    Valued Forum Contributor
    Join Date
    03-24-2014
    Location
    England
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    575

    Re: VBA : Date Format()

    its all about display formatting.

    If you use the format command to convert a date to dd.mmm.yyyy format then if you assign the resulting value to a string (or variant) variable then it'll show (eg) "01.Jan.2019". However, if you assign the resulting value to a date variable then it'll store it as "43466".

    If you input 43466 to a cell and then edit the cell options and configure it as a date it'll then show "1/1/2019". If you change the formatting to custom and specify "dd.mmm.yyyy", only then will it show as 01.Jan.2019.

  5. #5
    Registered User
    Join Date
    04-15-2019
    Location
    italy
    MS-Off Ver
    office 365
    Posts
    3

    Re: VBA : Date Format()

    The issue has been solved.
    Please find below the code to start from Week number, transform it date (dd/mm/yyyy) and finally transform it again to the format dd.mmm.yyyy

    Please Login or Register  to view this content.
    Hoping to be usefull for someone.

    thanks
    Rossofoco

  6. #6
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: VBA : Date Format()

    Hi again,

    Many thanks for your feedback - glad to hear that it seems to be working correctly for you now.

    Regards,

    Greg M

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Converting from the 1904 date format to the 1900 date format without losing data
    By Patty McJorst in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-01-2020, 12:53 PM
  2. [SOLVED] Converting Date & Time (General format) to Date (Date format)
    By supremenuts in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-22-2017, 12:55 PM
  3. Replies: 4
    Last Post: 04-29-2015, 08:36 AM
  4. Date Format Of User PC Changes Display format date of Excel File after update
    By waihar in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-24-2014, 03:09 AM
  5. Replies: 5
    Last Post: 02-08-2013, 11:06 AM
  6. Need Formula/VBA Code to convert date in String Format to Normal Date format
    By Karthik Sen in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-18-2012, 04:54 AM
  7. Column Range with date Format MM/DD/YYYY independate of sys date Format
    By gaursh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-18-2011, 03:31 AM

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