Format date and number
 

Can anyone tell me how to format a cell so that when a
date is entered it formats as a date and when a number is
entered it formats as a number.

If you use general it auto formats for a date but if you
enter a number in the same cell after a date has been
entered then it still thinks it's a date.

Any help will be appreciated.

Format date and number
 

Cindy,

You're out of luck. To Excel, dates are numbers, and numbers are numbers.
If you don't apply any formatting, Excel will format any strings entered as
valid dates as dates, then convert them to the proper number. Numbers it
will leave as"general".

So, enter your date as a valid date string, like 5/14/2004, and Excel will
know it is a date. However, once a cell ahs been autoformatted as a date, it
will stay a date. If you want to do what you describe, you do need to go
back and format the cell for, say, decimals.

HTH,
Bernie
MS Excel MVP

"Cindy" wrote in message
...
Can anyone tell me how to format a cell so that when a
date is entered it formats as a date and when a number is
entered it formats as a number.

If you use general it auto formats for a date but if you
enter a number in the same cell after a date has been
entered then it still thinks it's a date.

Any help will be appreciated.

Format date and number
 

Thank you for your help.

-----Original Message-----
Cindy,

You're out of luck. To Excel, dates are numbers, and
numbers are numbers.
If you don't apply any formatting, Excel will format any
strings entered as
valid dates as dates, then convert them to the proper
number. Numbers it
will leave as"general".

So, enter your date as a valid date string, like
5/14/2004, and Excel will
know it is a date. However, once a cell ahs been
autoformatted as a date, it
will stay a date. If you want to do what you describe,
you do need to go
back and format the cell for, say, decimals.

HTH,
Bernie
MS Excel MVP

"Cindy" wrote in
message
...
Can anyone tell me how to format a cell so that when a
date is entered it formats as a date and when a number
is
entered it formats as a number.

If you use general it auto formats for a date but if
you
enter a number in the same cell after a date has been
entered then it still thinks it's a date.

Any help will be appreciated.


.

Format date and number
 

the previous poster is correct but... if you know the
dates or range of dates that will be entered you can use
this sub in the worksheet module to check and if it = one
of the dates leave as date otherwise format as general,
adapt it to your needs

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const BD As Date = #1/1/2004#
Const ED As Date = #5/31/2004#
Dim num As Double
On Error GoTo EndSub
If Target.Value = BD And Target.Value <= ED Then
Target.NumberFormat = "m/d/yy"
Else
Target.NumberFormat = "General"
End If
EndSub:
End Sub



-----Original Message-----
Can anyone tell me how to format a cell so that when a
date is entered it formats as a date and when a number
is
entered it formats as a number.

If you use general it auto formats for a date but if you
enter a number in the same cell after a date has been
entered then it still thinks it's a date.

Any help will be appreciated.
.