locked
Excel 2007: Format Issue, Date Format in some fields will not change RRS feed

  • Question

  • Good Morning,

    In this spreadsheet there is a column of dates where I need to change the format from date to text. When I select the column only half the dates change.

    Changing from          Changing to

      8/27/1997                35669

    Any clues why this is happening and how to get around it?


    Chris Ward

    Monday, July 9, 2012 2:42 PM

Answers

  • Give this a try:

    Sub DateFixer()
    Dim T As Range, r As Range
    Set T = Union(Range("B2:B780"), Range("E2:E780"))
    For Each r In T
        If Not IsEmpty(r) Then
            r.Value = DateValue(r)
            r.NumberFormat = "General"
        End If
    Next
    End Sub


    gsnu201202

    • Marked as answer by KCDW Monday, July 9, 2012 7:52 PM
    Monday, July 9, 2012 6:30 PM
  • Hello Chris:

    Here's another way to accomplish the same thing:

    Sub fixText()
    Dim rngDateRange As Range
    Dim lngLastRowOfDates As Long
    Dim c As Range
    lngLastRowOfDates = Cells(Rows.Count, "B").End(xlUp).Row
    Set rngDateRange = Range(Cells(2, 2), Cells(lngLastRowOfDates, 2))
    
    Columns("B:B").NumberFormat = "m/d/yyyy"
    Columns("C:C").NumberFormat = "m/d/yyyy"
    
    For Each c In rngDateRange
        If c.Value <> "" Then
            c.Value = CDate(c.Value)
        End If
        
        If c.Offset(0, 1).Value <> "" Then
            c.Offset(0, 1).Value = CDate(c.Offset(0, 1).Value)
        End If
    Next c
    
    End Sub
    

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    • Marked as answer by KCDW Monday, July 9, 2012 7:52 PM
    Monday, July 9, 2012 7:17 PM

All replies

  • If some of the cells do not change from Date to Number, the cells are probably not true dates, but rather already just text.  For example if A1 contains 8/27/1997, then:

    Sub fixfirst()
    Range("A1").NumberFormat = "General"
    End Sub

    will make A1 appear as 35699.  But if the date in A1 is just Text, then:

    Sub fixText()
    Range("A1").Value = DateValue(Range("A1").Value)
    Range("A1").NumberFormat = "General"
    End Sub

    would be needed.


    gsnu201202

    Monday, July 9, 2012 2:51 PM
  • Would there be an issue running this when there are inconsistencies in format of the range?


    Chris Ward

    Monday, July 9, 2012 4:05 PM
  • Also, there are two ranges with the format issue can I do this in a single step to cover multiple ranges i.e. B2:B780 and E2:E780 (Row 1 has row titles)

    Chris Ward

    Monday, July 9, 2012 4:06 PM
  • That depends.  Please post some samples of the data you see in the cells and how they should be displayed after the macro runs.

    gsnu201202

    Monday, July 9, 2012 4:08 PM
  • Extending the macro to cover multiple regions is easy.  First we need to cover the cases the macro has to handle

    gsnu201202

    Monday, July 9, 2012 4:10 PM
  • Well the format looks like

    8/27/1997 where the cells are formatted as date however some cells retain the same look after changing the format to text or general. Others convert to a serial number. The data was exported from an Access file that has gone by the wayside. We are trying to get the format correct so we can upload to the new db. The new db will not accept the data as is due to the format. The new db is formatted to date as the Excel column is but the data in more than half the rows seems to not be date format.

    Your code compiled correctly and I changed the range to B1 but I get an error when I try running it

    Run-time Error '13'

    Type mismatch, hilighting

    Range("B1").Value = DateValue(Range("B1").Value)


    Chris Ward

    Monday, July 9, 2012 4:18 PM
  • It looks like there is an ' preceding each date to force the format to text. I think this because I manually changed 1 row by placing the insertion point at the beginning of the characters in the formula bar and hitting backspace and was then able to format as general to get a serial number. When I selected the Unod button this is what it showed.


    Chris Ward

    Monday, July 9, 2012 4:24 PM
  • Hello Chris:

    Try using the CDate function:

    First, format the target column as Date.  Then issue:  Target = CDate(Range("B1"))

    The good part about this function, which I just tested, is it will function properly if the source date is in text or Date format.

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Monday, July 9, 2012 4:27 PM
  • oKay I don't have to delete anything all I have to do is place the insertion point anywhere in the Formula bar then the one cell that is active at the time will format correctly.

    Chris Ward

    Monday, July 9, 2012 4:28 PM
  • Hi Rich,

    My terminology is limited, when you say issue target, do you mean write code like this;

    Sub fixText()

    Target = CDate(Range("B1"))

    End Sub

    Sorry my code blocks are not woirking on the internet


    Chris Ward

    Monday, July 9, 2012 4:33 PM
  • Chris:

    Sorry... by target, I mean something like:

    Range("C1") =

    Or 

    Sheets("MyWorksheet").Cells(5,1)

    Or Just

    Cells(2,1) = 

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Monday, July 9, 2012 4:37 PM
  • In your case, just try:

    (1) Format All of Column B as a Date

    (2) Then:

    Range("B1").Value = CDate(Range("B1"))

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Monday, July 9, 2012 4:38 PM
  • I get the same compile error with this

    Sub fixText()
    Range("B1").Value = CDate(Range("B1"))
    End Sub


    Chris Ward

    Monday, July 9, 2012 4:44 PM
  • Chris:

    Do you mean you get a run time error "13" again?  You must have a value that intrinsically is not a date.

    In your Visual Basic Editor, you can prove the CDate function works on text fields that actually are dates:

    For example,

    ? CDate("5/5/2012")
    5/5/2012 
    ? CDate(#5/5/2012#)
    5/5/2012 

    The above shows that the CDate can convert dates which are represented as Text or Date to a date.

    However, if you try:

    ? CDate("XXX"), you will get a Run Time Error 13.  So, take a close look at your date value.

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Monday, July 9, 2012 4:57 PM
  • Chris:

    Another option is to use the IsDate function.  It will let you know if a cell contains a date, even if the cell is formatted as Text.

    For example:

    ? IsDate("5/5/2012")
    True
    ? IsDate(#5/5/2012#)
    True
    ? IsDate("XXX")
    False

    So, you could do an IsDate check before you actually try to do the CDate function, and then you won't get the run time error.

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Monday, July 9, 2012 5:00 PM
  • Chris:

    You may want to consider getting a SkyDrive account and uploading a "sample" of the workbook so that the forum members can actually see what is going on.

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Monday, July 9, 2012 5:07 PM
  • Thank you Rich,

    I'll try using my phone to upload to Skydrive as I seem unable to access it on my work computer.

    Try This

    I hope it works


    Chris Ward

    Monday, July 9, 2012 6:25 PM
  • I'll try using my Phone to upload to skydrive

    Try this

    I hope it works


    Chris Ward

    Monday, July 9, 2012 6:27 PM
  • Give this a try:

    Sub DateFixer()
    Dim T As Range, r As Range
    Set T = Union(Range("B2:B780"), Range("E2:E780"))
    For Each r In T
        If Not IsEmpty(r) Then
            r.Value = DateValue(r)
            r.NumberFormat = "General"
        End If
    Next
    End Sub


    gsnu201202

    • Marked as answer by KCDW Monday, July 9, 2012 7:52 PM
    Monday, July 9, 2012 6:30 PM
  • gsnu,

    Your idea is partially working however there are a few blank fields and it errors on those but I can manually change the code each time it fails to the next row and it goes a little further. Is there a way to make the code ignore empty cells?

    Thanks!


    Chris Ward

    Monday, July 9, 2012 7:06 PM
  • Hello Chris:

    In your example, you were referencing Cell B1.  You would get an Error 13 on that cell because it is not a date, but a text header.  Notice that "Gary's Student" avoided referencing cell B1.  I would think that his approach should work.

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Monday, July 9, 2012 7:06 PM
  • Hello Chris:

    Here's another way to accomplish the same thing:

    Sub fixText()
    Dim rngDateRange As Range
    Dim lngLastRowOfDates As Long
    Dim c As Range
    lngLastRowOfDates = Cells(Rows.Count, "B").End(xlUp).Row
    Set rngDateRange = Range(Cells(2, 2), Cells(lngLastRowOfDates, 2))
    
    Columns("B:B").NumberFormat = "m/d/yyyy"
    Columns("C:C").NumberFormat = "m/d/yyyy"
    
    For Each c In rngDateRange
        If c.Value <> "" Then
            c.Value = CDate(c.Value)
        End If
        
        If c.Offset(0, 1).Value <> "" Then
            c.Offset(0, 1).Value = CDate(c.Offset(0, 1).Value)
        End If
    Next c
    
    End Sub
    

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    • Marked as answer by KCDW Monday, July 9, 2012 7:52 PM
    Monday, July 9, 2012 7:17 PM
  • Thanks for all the tips!

    Rich, I tried your code in a copy of the full spreadsheet (which puts the columns affected back to B & E). The result was strange. The good news is appearantly all the date fields are now true date fields however all the dollar amounts in the columns between were changed to dates in the year 1900.

    Here is the modified code (All I changed was "C:C" to "E:E") and with the rest of the columns back in column C is the Dollars.

    Can you hedlp me understand please?


    Chris Ward

    Monday, July 9, 2012 7:42 PM
  • Correction, it worked only in Column B

    Chris Ward

    Monday, July 9, 2012 7:46 PM
  • oKay,

    Rich, I put your code back the way you had it and I moved my columns around to match the code then it worked correctly.

    Thank you!


    Chris Ward

    Monday, July 9, 2012 7:51 PM
  • Chris:

    Glad that we could help.  Just for your understanding, since the real workbook has dates in Columns B and E (as you mentioned), here's the same code... documented... so you can get a feel for what's happening next time you need to write a macro.

    Option Explicit
    
    Sub fixText()
    Dim rngDateRange As Range
    Dim lngLastRowOfDates As Long
    Dim c As Range
    ' ****************************************************************
    ' Determine the last row in columm B
    ' ****************************************************************
    lngLastRowOfDates = Cells(Rows.Count, "B").End(xlUp).Row
    
    ' ****************************************************************
    ' Set a Range Variable for All Active Rows in Column B
    ' That Have Data
    ' ****************************************************************
    Set rngDateRange = Range(Cells(2, 2), Cells(lngLastRowOfDates, 2))
    
    ' ****************************************************************
    ' Format Columns B and E as Dates
    ' ****************************************************************
    Columns("B:B").NumberFormat = "m/d/yyyy"
    Columns("E:E").NumberFormat = "m/d/yyyy"
    
    ' ****************************************************************
    ' Process Column B
    ' ****************************************************************
    For Each c In rngDateRange
        If c.Value <> "" Then
            c.Value = CDate(c.Value)
        End If
    
    ' ****************************************************************
    ' Process Column E - It is Column B + 3 Columns Offset To The
    ' Right
    ' ****************************************************************
        If c.Offset(0, 3).Value <> "" Then
            c.Offset(0, 3).Value = CDate(c.Offset(0, 3).Value)
        End If
    Next c
    
    End Sub

    The "For Each C in rngDateRange" is one of the fastest and most powerful ways to move through a set of cells.  I learned this method from some of the Excel MVP's that post in this forum. Once you set the range, the "For Each" steps through them one at a time, and the variable C contains the value of the cell.  You can also use the Offset property to point to cells relative to the "C".

    What is also nice is that you can find out the row or column where C is currently processing by the property c.Row or C.Column.

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com


    • Edited by RichLocus Monday, July 9, 2012 9:57 PM
    Monday, July 9, 2012 9:56 PM