none
[VBA + Excel 2003] How to get DateValue(Now()) work when they're in yyyy/mm/dd format ? RRS feed

  • Question

  • Hi,

    I tried to use DateValue(Now()) to get today dates filter but I receive a runtime error message and I'm suspecting the fact that my column contain Dates in yyyy/mm/dd format and not mm/dd/yyyy?!

    Is there a way to fix this issue, please?

    Regards

    Thursday, October 6, 2011 8:11 PM

Answers

  • I am confused. Your subject is VBA + Excel 2003 and in the body you refer to a column of dates in yyyy/mm/dd format. I don't know what the correlation is.

    Does the question refer to VBA or something you are doing on a worksheet?

    Today's date in VBA is simply Date  (Not Today() as in a worksheet function.)

    DateValue in VBA refers to converting a string to a Date as in the following example from Help. Note that the string is enclosed in double quotes. You could also use a string variable in lieu of the date enclosed in double quotes. The string needs to be in a format that Excel can interpret as a date.

    MyDate = DateValue("February 12, 1969") 

    Also see VBA Help for DateSerial that will convert a year, month and day to a date.

    Note that worksheet Help and VBA Help are 2 differnt things. You must be in the worksheet window when opening worksheet Help and in the VBA editor when you open the VBA Help.

    When you have changed windows between worksheet and VBA and vice versa, you need to re-open the Help otherwise you might not find what you are after.


    Regards, OssieMac
    Friday, October 7, 2011 6:18 AM

All replies

  • Are these values Text or just Date values shown in a different way?

    If they are Date values, you can simply use Today() function to filter on current date.

     

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Thursday, October 6, 2011 8:51 PM
    Moderator
  • I am confused. Your subject is VBA + Excel 2003 and in the body you refer to a column of dates in yyyy/mm/dd format. I don't know what the correlation is.

    Does the question refer to VBA or something you are doing on a worksheet?

    Today's date in VBA is simply Date  (Not Today() as in a worksheet function.)

    DateValue in VBA refers to converting a string to a Date as in the following example from Help. Note that the string is enclosed in double quotes. You could also use a string variable in lieu of the date enclosed in double quotes. The string needs to be in a format that Excel can interpret as a date.

    MyDate = DateValue("February 12, 1969") 

    Also see VBA Help for DateSerial that will convert a year, month and day to a date.

    Note that worksheet Help and VBA Help are 2 differnt things. You must be in the worksheet window when opening worksheet Help and in the VBA editor when you open the VBA Help.

    When you have changed windows between worksheet and VBA and vice versa, you need to re-open the Help otherwise you might not find what you are after.


    Regards, OssieMac
    Friday, October 7, 2011 6:18 AM