• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • 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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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