Date always Jan. 1900

**L62H66** · 01-30-2011, 03:04 PM

First off Hello to all here,
Secondly, I searched the forum for a match to my question but found none so here it is:

I am trying to use the DAY function to create a bill paying calendar but no matter what date code I put in I get one of the 31 days of January 1900. For example I use the following specific DAY function:

=DAY(40000)

The result in the cell:

6-Jan-1900

No matter what date code I enter into the function, the month and year will always be January, 1900. I am following the help section of Excel to the letter. What is wrong?
Thanks
Lou

The version of excel is 2002 (10.6866.6867) SP3

**DonkeyOte** · 01-30-2011, 03:10 PM

DAY returns the Day of the Month so will always be 1-31

On a 1900 Date System 40000 equates to 6th Jul 09, DAY is 6 which formatted as Date is 6-Jan-1900 - if you want to see 6 format as General

If the above does not resolve your issue perhaps outline what you're trying to achieve ?

**L62H66** · 01-30-2011, 04:39 PM

Ok, that would make sense - I did manage to format the cell for different outputs to the same DAY function and did arrive at a single number output in the general format. However, it might be helpful to explain what I hope to achieve. I have created a calendar that contains 6 cells per each day square of the month. I want the topmost cell in each calendar day to contain the date. How is this done so that I don't have to manually enter them in? I don't understand the date code beyond they reflect the number of days since Jan. 1st, 1900 (or 1904 for Macs). If I try to put a date code in the parentheses for the DATE function, I get a 'too few arguments' error. Help me, please, simplify this task. I do know how to use functions in calculating figures but have never applied the DATE or DAY functions and this is making me crazy. Thanks.
Lou

ADDENDUM

Here is something else that is odd. I put the following function into a cell:

=DATE(1,30,2011)

When I format the cell, I get the following:

3258 - general format

12/1/1908 - date format

That makes no sense whatsoever.

**DonkeyOte** · 01-30-2011, 06:50 PM

Dates are just serial numbers - the format determines the appearance of the # - eg Date/Number.
If you want to calculate based off dates it's simply a case of conducting standard arithmetic operations - 1 being 1 day, so:

Please Login or Register  to view this content.

will add one day to the date value in A1

It follows that Time is Decimal (1 = 24 hours) so:

Please Login or Register  to view this content.

would add 36 hours to the value in A1

Originally Posted by L62H66

Here is something else that is odd. I put the following function into a cell:

=DATE(1,30,2011)

When I format the cell, I get the following:

3258 - general format

12/1/1908 - date format

That makes no sense whatsoever.

The Date function has three parameters: Year, Month & Day - in that order.

Please Login or Register  to view this content.

the 2011th date of the 30th month of year 1 is 12-Jan-1908.

Please Login or Register  to view this content.

is the 30th Jan 2011

You can of course equally enter: 1/30 as a constant.

**L62H66** · 01-30-2011, 07:05 PM

Thanks for the reply. I understand that much. On my worksheet, however, it is not comprehending the date correctly. It is saying, in essence, that 1, 30, 2011, is the 3258th day from 1, 1, 1900. When the cell is formatted as a date, it says Dec. 12, 1908. Something is screwed up here.

**DonkeyOte** · 01-30-2011, 07:27 PM

Nothing is awry, quoting from prior post:

Originally Posted by D.O

The Date function has three parameters: Year, Month & Day - in that order.

Code:
=DATE(1,30,2011)
the 2011th day of the 30th month of year 1 (1901) is 12-Jan-1908.

I'd suggest having a read through of the Help files re: Date function - the order of the parameters does not vary - you must enter Year, Month & Day in that order.