Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Date Format

  1. #1
    Guest

    Default

    When I enter a date in a cell it always displays as a number. This happens however I have the cell formatted and however I enter the date. ie

    I enter 01/02/2002 I get 37288

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-19 07:35, Anonymous wrote:
    When I enter a date in a cell it always displays as a number. This happens however I have the cell formatted and however I enter the date. ie

    I enter 01/02/2002 I get 37288
    How do you have the cell formatted? You state it doesn't matter about your formatting, but if the cell is formatted as date it can't return 37288. Double check that your cell is not formatted as General.
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    Guest

    Default

    The cell is formatted as a date but it doen't matter I could type in 01 March 2001 and still get a huge number appear.

  4. #4
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-19 07:50, Anonymous wrote:
    The cell is formatted as a date but it doen't matter I could type in 01 March 2001 and still get a huge number appear.
    Can you email me the spreadsheet and I'll look at it?
    Barrie Davidson e-mail

  5. #5
    Guest

    Default

    I have worked out what is happening. The number being displayed is the number of days that have between 1 Jan 1900 and the date I enter. How do I stop it doing that and why did it start.

    Sorry I can't e-mail you the spreadsheet it contains personal details.

  6. #6
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-19 08:34, Anonymous wrote:
    I have worked out what is happening. The number being displayed is the number of days that have between 1 Jan 1900 and the date I enter. How do I stop it doing that and why did it start.

    Sorry I can't e-mail you the spreadsheet it contains personal details.
    Does the spreadsheet contain macros? I'm thinking maybe some code has been attached to the worksheet.

    Can you clear out the personal details on the spreadsheet?
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  7. #7
    New Member
    Join Date
    Feb 2002
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here is a little bit of information that I had found yesterday while researching my problem. I found it in my Help file from Excel 2000.

    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.

    Date
    system
    First date
    Last date
    1900 January 1, 1900
    (serial value 1) December 31, 9999
    (serial value 2958465)
    1904 January 2, 1904
    (serial value 1) December 31, 9999
    (serial value 2957003)


    Notes

    Two-digit years When you enter a date in a cell and you enter only two digits for the year, Excel interprets the year as follows:

    The years 2000 through 2029 if you type 00 through 29 for the year. For example, if you type 5/28/19, Excel assumes the date is May 28, 2019.


    The years 1930 through 1999 if you type 30 through 99 for the year. For example, if you type 5/28/98, Excel assumes the date is May 28, 1998.
    If you are using Microsoft Windows 98 or Microsoft Windows 2000, you can, without the assistance of your system administrator, change the way two-digit years are interpreted.

    Four-digit years To ensure that year values are interpreted as you intended, type year values as four digits (2001, rather than 01). By entering four digits for the years, Excel won't interpret the century for you.

    The DATE worksheet function If you need to manipulate a part of a date such as the year or month within a formula, you can use the DATE worksheet function.

    The TIME worksheet function If you need to manipulate a part of a time such as the hour or minute within a formula, you can use the TIME worksheet function.

    Display four-digit years by default By default, as you enter dates in a workbook, the dates are formatted to display two-digit years. You can use Windows Control Panel to change the default date format to display four-digit years instead of two-digit years.

    The Year 2000 The Microsoft Year 2000 Resource Center provides information about how Microsoft products are affected by the arrival of the year 2000. Learn about the Microsoft Year 2000 Resource Center.


    Also:

    DATE
    See Also

    Returns the serial number that represents a particular date.

    For more information about how Microsoft Excel uses serial numbers for dates, see the Remarks section.

    Syntax

    DATE(year,month,day)

    Year The year argument can be one to four digits. Excel interprets the year argument according to the date system you are using. By default, Excel for Windows uses the 1900 date system; Excel for the Macintosh uses the 1904 date system.

    For the 1900 date system:

    If year is between 0 (zero) and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example, DATE(100,1,2) returns January 2, 2000 (1900+100).


    If year is between 1900 and 9999 (inclusive), Excel uses that value as the year. For example, DATE(2000,1,2) returns January 2, 2000.


    If year is less than 0 or is 10000 or greater, Excel returns the #NUM! error value.
    For the 1904 date system:

    If year is between 4 and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example, DATE(100,1,2) returns January 2, 2000 (1900+100).


    If year is between 1904 and 9999 (inclusive), Excel uses that value as the year. For example, DATE(2000,1,2) returns January 2, 2000.


    If year is less than 4 or is 10000 or greater or if year is between 1900 and 1903 (inclusive), Excel returns the #NUM! error value.
    Month is a number representing the month of the year. If month is greater than 12, month adds that number of months to the first month in the year specified. For example, DATE(1998,14,2) returns the serial number representing February 2, 1999.

    Day is a number representing the day of the month. If day is greater than the number of days in the month specified, day adds that number of days to the first day in the month. For example, DATE(1998,1,35) returns the serial number representing February 4, 1998.

    Remarks

    Excel stores dates as sequential serial numbers so that it can perform calculations on them. Excel stores January 1, 1900, as serial number 1 if your workbook uses the 1900 date system. If your workbook uses the 1904 date system, Excel stores January 1, 1904, as serial number 0 (January 2, 1904, is serial number 1). For example, in the 1900 date system, Excel stores January 1, 1998, as serial number 35796 because it is 35,795 days after January 1, 1900. Learn more about how Microsoft Excel stores dates and times.


    The DATE function is most useful in formulas where year, month, and day are formulas, not constants.

    Examples

    Using the 1900 date system (the default in Excel for Windows), DATE(1998,1,1) equals 35796, which is the serial number corresponding to January 1, 1998.

    Using the 1904 date system (the default in Microsoft Excel for the Macintosh), DATE(1998,1,1) equals 34334, which is the serial number corresponding to January 1, 1998.



Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •