changing contents of column when Formatting

Re: changing contents of column when Formatting

Are you sure Excel is regarding them as dates?

Re: changing contents of column when Formatting

Try select the column selct edit>clear>formats. This will set you to text numbers and then format the column as needed. Good luck

Re: changing contents of column when Formatting

where are your 300 dates coming from? manually entered,imported, etc.

Re: changing contents of column when Formatting

Access?

Re: Changing contents of column while Formating

If you're doing it manually (not via VBA), then you can highlight all of the cells you need to change, right-click and select format cells. Any changes you make here will apply to all cells. Excel should recognize "January 2006" as a date and formatting it properly should show "Jan-06"; you don't need to explicitly change the value of the cell. Hope this helps.

Re: Changing contents of column while Formating

If the changes apply to all of column b, just right-click the B button and changes will apply to the whole column.

Re: Changing contents of column while Formating

Moved to the Excel / VBA forum

jiuk

Re: Changing contents of column while Formating

I believe the problem is that the values, at import time, were not recognized as a date but as normal text. Try right-clicking on the tab of your worksheet at the bottom (normally named Sheet1, Sheet2, etc) and click on "view code". Microsoft Visual Basic should pop up, and you should see a white page if you don't have any macros in your workbook. Paste the following code in the white space that you see:

Public Sub ConvertToDates()
Dim tempRange As Range
Set tempRange = Columns("B:B")
For Each cell In tempRange
    If IsDate(cell) Then
        cell = CDate(cell)
    End If
Next cell
End Sub

Press the play button with your cursor somewhere in the code. Then return to your Excel spreadsheet and you should find the date formatted properly. The code looks through all the cells in column b, checks if the value looks like a date, and if it does, it will convert it into a date. You'll have to do whatever formatting you still need to do in Excel though. Feel free to delete the code once you're done with it.

Hope this helps.

Re: changing contents of column when Formatting

I believe the problem is that the values, at import time, were not recognized as a date but as normal text. Try right-clicking on the tab of your worksheet at the bottom (normally named Sheet1, Sheet2, etc) and click on "view code". Microsoft Visual Basic should pop up, and you should see a white page if you don't have any macros in your workbook. Paste the following code in the white space that you see:

Public Sub ConvertToDates() 
    Dim tempRange As Range 
    Set tempRange = Columns("B:B") 
    For Each cell In tempRange 
        If IsDate(cell) Then 
            cell = CDate(cell) 
        End If 
    Next cell 
End Sub

Press the play button with your cursor somewhere in the code. Then return to your Excel spreadsheet and you should find the date formatted properly. The code looks through all the cells in column b, checks if the value looks like a date, and if it does, it will convert it into a date. You'll have to do whatever formatting you still need to do in Excel though. Feel free to delete the code once you're done with it.

Hope this helps.

Re: changing contents of column when Formatting

PLEASE DO NOT START DUPLICATE THREADS ABOUT THE SAME TOPIC IN DIFFERENT FORUMS.

I have merged the two threads.