Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number format changes to date by itself | Excel Discussion (Misc queries) | |||
format number to date | Excel Worksheet Functions | |||
How do you format a number into a date? | Excel Discussion (Misc queries) | |||
* next to date format in Formatcells Number tab | Excel Discussion (Misc queries) | |||
Excel: I enter date and format for date, but shows as number | Excel Discussion (Misc queries) |