In Excel Sheet how to convert the date format of a cell to general format when its value is changed from date to text(or number)?

Question

I find this very strange in excel sheet (or may be its just me), by default all the CELLS in the excel sheet are in GENERAL format (G) and when I enter date value (1/1/2012) in a cell the format gets changed to date (D4) which is perfect. But when I change back the value to some text value (e.g: train) the format does not change to general format (or text format), it still stays as date format (D4).

Should I enable any setting in my Excel to change the format from date to general when a cell's value is changed from date to text ?

any help will be highly appreciated.

Answer 1

No, you can't do this. It is a one-time thing.

Answer 2

Have you considered this instead?

Change the NumberFormat on entering a cell to "General" NumberFormat everytime.

(You would have to enter the cell in edit mode to change from a date to text anyway, so it is not significantly different)

Instead of checking the format when you are leaving the cell.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    ActiveCell.NumberFormat = "General"
End Sub

Of course this can create other problems with other cells you don't want as "General", but you could exclude ranges, pretty easily by testing...

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If ActiveCell.Column <> 4 Then
      ActiveCell.NumberFormat = "General"
    End If
End Sub

When you enter a date in an ActiveCell, Excel will still convert it to date format on your exiting, but because it is a "General" format to begin with a serial number won't convert to a date, which seems to be the issue you have now.

The biggest disadvantage is that selecting an existing date will convert it to "General", but while still in the cell you can always right-click and format it as date, if you forgot to notice what the date was before exiting.

If you still want to explore formatting the cell as you leave it you could try this as well

Store the current ActiveCell as a Public variable in a module so that when you leave it, you can use it to check whether it is a date format

The Cell function might be the easiest way to test for a date format

aCell = Application.Evaluate("=CELL(""format""," & previousCell & ")")
If Left(aCell, 1) <> "D" Then previouseCell.NumberFormat = "General"

Answer 3

You can also use this worksheet function:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not IsNumeric(Target.Value) And Not IsDate(Target.Value) Then
    Target.NumberFormat = "General"
  End If
End Sub

If the value is a string then the General format will be set.