Date Format

Feb 19th, 2002, 08:35 AM

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

Barrie Davidson · Feb 19th, 2002, 08:38 AM

Quote:

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.

Feb 19th, 2002, 08:50 AM

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.

Barrie Davidson · Feb 19th, 2002, 09:20 AM

Quote:

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

Feb 19th, 2002, 09:34 AM

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.

Barrie Davidson · Feb 19th, 2002, 09:38 AM

Quote:

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?

J Lowe · Feb 19th, 2002, 09:40 AM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 19th, 2002, 08:35 AM	#1
Guest Posts: n/a	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

Feb 19th, 2002, 08:50 AM	#3
Guest Posts: n/a	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.

Feb 19th, 2002, 09:34 AM	#5
Guest Posts: n/a	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.

Feb 19th, 2002, 09:40 AM	#7
J Lowe New Member Join Date: Feb 2002 Posts: 27	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.