Excel VBA date formats

Question

I ve got a spreadsheet that contains a number of dates   These generally appear in either mm dd yyyy or mm dd yyyy hh mm   The problem is that the dates aren t always put in correctly and I want to have checks to make sure they are dates in the code   My original thought was to use IsDate to check or CDate but this didn t seem to work  it was still returning strings instead of dates   I ve since set up a small experiment which shows that these functions don t work the way I expect them to   Methodology is    In a cell A1 I enter the formula  DATE 2013 10 28  Cell B1 formula  A1 1 which should equal a number  41575  Run this little script  Sub test    MsgBox   Start    amp  TypeName ActiveCell Value   amp       amp  IsDate ActiveCell Value    ActiveCell Value   Format ActiveCell Value   mm dd yyyy   MsgBox   After format     amp  TypeName ActiveCell Value   amp       amp  IsDate ActiveCell Value    ActiveCell Value   CDate ActiveCell Value  MsgBox   After Cdate     amp  TypeName ActiveCell Value   amp       amp  IsDate ActiveCell Value    End Sub    When the script starts the cell is a of type date and IsDate returns true   After it is run through Format it is of type string but IsDate still returns true   CDate will also convert the cell to a string   Cell B1 will also now return 0  since its a string 1    So I guess to summarize the questions    Why are Format and CDate changing my cells to strings  How can I ensure that a cell will return a date value and not just a string that looks like a date

User · Answer

To ensure that a cell will return a date value and not just a string that looks like a date  first you must set the NumberFormat property to a Date format  then put a real date into the cell s content   Sub test date or String    Set c   ActiveCell  c NumberFormat        c Value   CDate  03 04 2014      Debug Print c Value  amp    is a    amp  TypeName c Value   C is a String  c NumberFormat    m d yyyy     Debug Print c Value  amp    is a    amp  TypeName c Value   C is still a String  c Value   CDate  03 04 2014      Debug Print c Value  amp    is a    amp  TypeName c Value   C is a date     End Sub

User · Answer

Use value cellref  on the side to evaluate the cells  Strings will produce the   Value  error  but dates resolve to a number  e g  43173

User · Answer

It s important to distinguish between the content of cells  their display format  the data type read from cells by VBA  and the data type written to cells from VBA and how Excel automatically interprets this   See e g  this previous answer   The relationship between these can be a bit complicated  because Excel will do things like interpret values of one type  e g  string  as being a certain other data type  e g  date  and then automatically change the display format based on this  Your safest bet it do everything explicitly and not to rely on this automatic stuff    I ran your experiment and I don t get the same results as you do  My cell A1 stays a Date the whole time  and B1 stays 41575  So I can t answer your question  1  Results probably depend on how your Excel version settings choose to automatically detect change a cell s number format based on its content    Question  2   How can I ensure that a cell will return a date value   well  not sure what you mean by  return  a date value  but if you want it to contain a numerical value that is displayed as a date  based on what you write to it from VBA  then you can either    Write to the cell a string value that you hope Excel will automatically interpret as a date and format as such  Cross fingers  Obviously this is not very robust  Or   Write a numerical value to the cell from VBA  obviously a Date type is the intended type  but an Integer  Long  Single  or Double could do as well  and explicitly set the cells  number format to your desired date format using the  NumberFormat property  or manually in Excel   This is much more robust     If you want to check that existing cell contents can be displayed as a date  then here s a function that will help   Function CellContentCanBeInterpretedAsADate cell As Range  As Boolean     Dim d As Date     On Error Resume Next     d   CDate cell Value      If Err Number  lt  gt  0 Then         CellContentCanBeInterpretedAsADate   False     Else         CellContentCanBeInterpretedAsADate   True     End If     On Error GoTo 0 End Function   Example usage   Dim cell As Range Set cell   Range  A1    If CellContentCanBeInterpretedAsADate cell  Then     cell NumberFormat    mm dd yyyy hh mm  Else     cell NumberFormat    General  End If

User · Answer

Thanks for the input   I m obviously seeing some issues that aren t being replicated on others machines   Based on Jean s answer I have come up with less elegant solution that seems to work   Since if I pass the cell a value directly from cdate  or just format it as a number it leaves the cell value as a string I ve had to pass the date value into a numerical variable before passing that number back to the cell    Function CellContentCanBeInterpretedAsADate cell As Range  As Boolean     Dim d As Date     On Error Resume Next     d   CDate cell Value      If Err Number  lt  gt  0 Then         CellContentCanBeInterpretedAsADate   False     Else         CellContentCanBeInterpretedAsADate   True     End If     On Error GoTo 0 End Function   Example usage   Dim cell As Range dim cvalue as double Set cell   Range  A1    If CellContentCanBeInterpretedAsADate cell  Then     cvalue   cdate cell value      cell value   cvalue     cell NumberFormat    mm dd yyyy hh mm  Else     cell NumberFormat    General  End If

User · Answer

Format converts the values to strings  IsDate still returns true because it can parse that string and get a valid date   If you don t want to change the cells to string  don t use Format   IOW  don t convert them to strings in the first place   Use the Cell NumberFormat  and set it to the date format you want displayed   ActiveCell NumberFormat    mm dd yy      Outputs 10 28 13 ActiveCell NumberFormat    dd mm yyyy    Outputs 28 10 2013

[vba] Excel VBA date formats

Examples related to vba

Examples related to date

Examples related to excel