Format date and number

C

Cindy

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.
 
B

Bernie Deitrick

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
 
G

Guest

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




.
Click to expand...
 
S

Steve

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
 
Top