• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

Format issue

Hi,

You can see from my attached file that there is a time stamp on the bottom dates. How can i format this column so it does not show the time, i just want it to show the date.

Thanks
Seamus
test.xls
0
Seamus2626
Asked:
Seamus2626
  • 4
  • 2
  • 2
  • +2
2 Solutions
 
zorvek (Kevin Jones)ConsultantCommented:
The values have been tagged as a psuedo text value. You will have to edit each one to get Excel to store the value as a non-text value and thus display it using the set date format.

Kevin
0
 
gtglonerCommented:
You could use this formula in cell B119 and copy down:
=LEFT(A119,10)
0
 
gtglonerCommented:
... then Copy and Paste Special-Values back into A column
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jppintoCommented:
Just select the rows that have this date/times (only the rows that have dates). On the formula bar enter: 13/6 and press Ctrl+Enter. It will enter this date in all of the rows selected at once.

jppinto
0
 
jppintoCommented:
This can be done because all of this rows have the same date on it!
0
 
Seamus2626Author Commented:
I was thinking that Jpinto! But i need a more flexible solution, i can use the left function and paste back in but i would much rather format the column, so i am wondering is there Custom Format for this?

Thanks
Seamus
0
 
zorvek (Kevin Jones)ConsultantCommented:
The problem is that the data is stored as a text value even though the format of the column is a date format. You must be copying and pasting this data in from another source.

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Here is a macro solution. Add this macro to the workbook VBA project, select the problem dates, and run the macro.

Public Sub FixDates()

    Dim Cell As Range
   
    For Each Cell In Selection
        Cell.Value = Cell.Value
    Next Cell

End Sub

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
To add VBA code to a regular or general module in an Excel workbook, press ALT+F11 to open the VBA development environment (VBE). Select the menu command Insert->Module to create a new VBA module. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

To run a macro from Excel, select the menu command Tools->Macro->Macros or press ALT+F8. A dialog box appears listing all available macros. Find the desired macro and select it. Click the Run command button to start the macro.

Kevin
0
 
byundtCommented:
1) Copy a blank cell
2) Select the entire column with your data
3) Paste Special with the Add option
4) Format with the Short Date format


Alternate macro to automate the above step in the column containing the active cell:
Sub ConvertDates()
Cells(Rows.Count, Columns.Count).Copy
With ActiveCell.EntireColumn
    .PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
    .NumberFormat = "m/d/yyyy"
End With
End Sub

Open in new window

0
 
Seamus2626Author Commented:
Hey zorvek, Byundt solution was much more powerful

Thank you everyone for your help

Thanks
Seamus
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now