Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Date format issue
I have the cell formatted to Date 03/14/01, however, when I type in 053106
and tab it displays as 05/24/45. Seems like there is a hidden formula but I checked and the worksheet isn't protected and has no hidden formulas. Suggestions please. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Date format issue
Hi Cindy
your cell is formated as DATE, try ti format it as a text to include 053106 or general to see 53106, note that the genderal will supress the left zero. hope this helps, regards from Brazil Marcelo "CindyLF1" escreveu: I have the cell formatted to Date 03/14/01, however, when I type in 053106 and tab it displays as 05/24/45. Seems like there is a hidden formula but I checked and the worksheet isn't protected and has no hidden formulas. Suggestions please. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Date format issue
06Hey Cindy...
The way Excel calulates dates is based off of a numbering sequence assigned to each specific date. Here is an extract from the Excel Help tool... if you look at todays date 06/12/06, Excel stores this date as 38880.00 so if all you do is enter the string 053106, then Excel thinks that you are entering a number string equivalent to a date... that date assigned to the numberic string 53106 happens to be 05/24/2045... read the passgae below to better understand dates in Excel... How Microsoft Excel stores dates and times Microsoft Excel stores dates as sequential numbers known as serial values. Excel stores times as decimal fractions because time is considered a portion of a day. Dates and times are values and, therefore, can be added, subtracted, and included in other calculations. For example, to determine the difference between two dates, you can subtract one date from the other. You can view a date as a serial value and a time as a decimal fraction by changing the format of the cell that contains the date or time to General format. Excel supports two date systems: the 1900 and 1904 date systems. The default date system for Microsoft Excel for Windows is 1900. To change to the 1904 date system, click Options on the Tools menu, click the Calculation tab, and then select the 1904 date system check box. The following table shows the first date and the last date for each date system and the serial value associated with each date. -- Thanks for your help - Joe Mac "CindyLF1" wrote: I have the cell formatted to Date 03/14/01, however, when I type in 053106 and tab it displays as 05/24/45. Seems like there is a hidden formula but I checked and the worksheet isn't protected and has no hidden formulas. Suggestions please. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Date format issue
Cindy
You cannot enter dates in the manner which you are attempting without using VBA coding. You must enter dates using some sort of seperator like / or - For VBA see Chip Pearson's site. http://www.cpearson.com/excel/DateTimeEntry.htm Also see Chip's site for an explanation of how Excel computes dates. http://www.cpearson.com/excel/datetime.htm#AddingDates Gord Dibben MS Excel MVP On Mon, 12 Jun 2006 08:17:02 -0700, CindyLF1 wrote: I have the cell formatted to Date 03/14/01, however, when I type in 053106 and tab it displays as 05/24/45. Seems like there is a hidden formula but I checked and the worksheet isn't protected and has no hidden formulas. Suggestions please. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell date format doesnt change | Excel Worksheet Functions | |||
Enforcing a date format on a cell/column | Excel Discussion (Misc queries) | |||
DATE FORMAT issue | Excel Worksheet Functions | |||
format date in excel | Excel Discussion (Misc queries) | |||
Why Does Date Format Change on Chart | Excel Discussion (Misc queries) |