Date Format Problem

  • Thread starter Thread starter Simon Topping
  • Start date Start date
S

Simon Topping

Hello,

I've imported a set of data from an external source into
Excel. One of the fields is the date and it is displayed
in Excel as:- 2003-03-25 11:34:04.

When I try to change this however to 'dd-mmm-yy' format
there is no change. It also means that calculations based
on this date can't be made.

If I double click the cell with the '2003-03-25 11:34:04'
date (as if I was to edit the contents) and immediately
hit enter (thus not making any changes), then the format
does change to the 'dd-mmm-yy' format and calculations can
be made.


Is there any way to change the imported date type to one
Excel recognises with ease without doing each cell
individually?

Thanks


Simon Topping
 
try
Sub ConvertThem() 'Harald Staff
Dim C As Range
For Each C In Intersect(Selection, _
ActiveSheet.UsedRange)
If Not C.HasFormula Then
If IsNumeric(C.Value) Then
C.Value = C.Value * 1
End If
End If
Next
End Sub
 
Note that if the OP imported these from the web there might be some invisble
html characters,
if that's the case David McRitchie's macro TrimALL might help

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
 
Copy a blank cell. Select your date cells. Choose Edit/Paste
Special, selecting the Add and Values radio buttons. Click OK
 
J.E. McGimpsey said:
Copy a blank cell. Select your date cells. Choose Edit/Paste
Special, selecting the Add and Values radio buttons. Click OK
Click to expand...


J.E.

That works great and has sorted an issue I've had for a while. Appreciate
it.

On a related subject would you have any idea how to convert an imported
column of data that is in the American format (mm/dd/yyyy) to European
(dd/mm/yyyy)? (Excel 97)

Many thanks, Shaun.
 
Select your column. Choose Data/Text to columns. Click Next, Next,
then select MDY from the date dropdown. Click Finish
 
J.E. McGimpsey said:
Select your column. Choose Data/Text to columns. Click Next, Next,
then select MDY from the date dropdown. Click Finish
Click to expand...

Thankyou - will try it shortly. Appreciate it. Shaun.
 
Select the dates, Format->Custom "Type" enter the format you want (if it's
not already there)

--
Regards;
Rob

Please reply to the NG.
I'm already up to my eyeballs in
Nigerian/South African get rich
letters as well as "Microsoft Critical Updates" et al.
 
Back
Top