Check if cell contains DATE

    I'm wondering how would you check to see if a cell contains a date format?


    is there a function similar to IsNumeric for dates?



    I figure, if it is a date, it would be automatically formatted as a DATE FORMAT.


    My main concern is, I need to check if a cell is reckognized as a DATE in excel.

    Can't any positive numeric value be construed to be a date by Excel? What would not be recognized as a date by Excel?

    If you highlight the cell and go to format cells, doesn't the cell type show automatically? Then you can change it from that to what you want it to be recognized as.

    Ok, here is my problem.



    When a cell is labelled as 24_MAY_04 or something similar, excel does not reckognize it as a DATE FORMAT. even if the Cell format is set to a DATE, it is still not a DATE FORMAT.


    If by chance a user enters this type of format in a cell, my macros will crash since the dates are incorrect. It crashes because I use the Data in a chart, and when it hits these values, Excel does not reckognize it as a date format.

    In your example, this is not a numeric value. That should allow you to test for valid dates. If you need the dates bounded in some way, say only dates this year, then you could add additional checking once you determine the value is numeric.

    You can use Data Validation to ensure that a date in the correct format is entered:


    Data > Data Validation > Date


    Hope this helps!

    Reading this post my advice would be to first get the data set correct if it’s a date FORCE Excel to show as a date how you want, there millions of ways to do this, some offered here. Only once you have the date reading as you want then test the cells as to validity to your requirements, even if this means on a different sheet or additional viewable / hidden columns if you can not edit the date that is causing the problems, I do not thin Excel will enjoy the date formatting you have right now.


    You can write a function to test a date but will only work if the cell contains a real recognisable date.



    Rdgs


    Jack

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login