Am trying to understand why is that when I have a variable MaxDate
(undeclared) and is has a value of 38697 and if I use
=TEXT(MaxDate,"mm/dd/yy") I get the correct value of 12/12/09 but if I
use function =CDATE(MaxDate) I get 12/11/2005 as an output? Also the
format command didn't work either to convert MaxDate with the code
=format(MaxDate, "mm/dd/yy") as it also gave 12/11/2005 when the
correct output was 12/12/09. I am trying to understand this.

Thanks,
Chet

Assuming you are using XL2003 or lower... if you click to Tools/Options on the menu bar, then select the Calculation tab and uncheck the "1904 date system" check box, all your calculations will agree. Note, however, if you have other dates in your workbook, those dates will change also.

--
Rick (MVP - Excel)


"Chet" <(E-Mail Removed)> wrote in message news:cd4ea64c-337c-41d7-b77c-(E-Mail Removed)...
> Am trying to understand why is that when I have a variable MaxDate
> (undeclared) and is has a value of 38697 and if I use
> =TEXT(MaxDate,"mm/dd/yy") I get the correct value of 12/12/09 but if I
> use function =CDATE(MaxDate) I get 12/11/2005 as an output? Also the
> format command didn't work either to convert MaxDate with the code
> =format(MaxDate, "mm/dd/yy") as it also gave 12/11/2005 when the
> correct output was 12/12/09. I am trying to understand this.
>
> Thanks,
> Chet

Sub Chet()
Dim MaxDate As Long
MaxDate = 38697
MsgBox (Format(MaxDate, "mm/dd/yy"))
MsgBox (CDate(MaxDate))
End Sub

both display 12/11/2005 because that is the correct value. If you desire
12/12/2009, then start with 40159

--
Gary''s Student - gsnu201001


"Chet" wrote:

> Am trying to understand why is that when I have a variable MaxDate
> (undeclared) and is has a value of 38697 and if I use
> =TEXT(MaxDate,"mm/dd/yy") I get the correct value of 12/12/09 but if I
> use function =CDATE(MaxDate) I get 12/11/2005 as an output? Also the
> format command didn't work either to convert MaxDate with the code
> =format(MaxDate, "mm/dd/yy") as it also gave 12/11/2005 when the
> correct output was 12/12/09. I am trying to understand this.
>
> Thanks,
> Chet
> .
>

On Sun, 21 Feb 2010 11:03:22 -0800 (PST), Chet <(E-Mail Removed)> wrote:

>Am trying to understand why is that when I have a variable MaxDate
>(undeclared) and is has a value of 38697 and if I use
>=TEXT(MaxDate,"mm/dd/yy") I get the correct value of 12/12/09 but if I
>use function =CDATE(MaxDate) I get 12/11/2005 as an output? Also the
>format command didn't work either to convert MaxDate with the code
>=format(MaxDate, "mm/dd/yy") as it also gave 12/11/2005 when the
>correct output was 12/12/09. I am trying to understand this.
>
>Thanks,
>Chet

It occurs because in your Excel options, you have selected to use the 1904 date
system. When you use the TEXT command, it being a Worksheet Function, it uses
whichever date system you have set into your Excel Options. In the 1904 date
system, "0" = 1/1/1904

However, CDATE, being a native VBA method, converts dates according to the
method in VBA, in which "Dates are stored as part of a real number. Values to
the left of the decimal represent the date; values to the right of the decimal
represent the time. Negative numbers represent dates prior to December 30,
1899."

--ron

Thanks to everyone on your answers.. it is very logical now!... Chet

On Sun, 21 Feb 2010 15:17:54 -0500, Ron Rosenfeld
<(E-Mail Removed)> wrote:

> Negative numbers represent dates prior to December 30,
>1899."


Would that not correctly be: December 31, 1899?

On Sun, 21 Feb 2010 14:06:54 -0800, CellShocked
<(E-Mail Removed)> wrote:

>On Sun, 21 Feb 2010 15:17:54 -0500, Ron Rosenfeld
><(E-Mail Removed)> wrote:
>
>> Negative numbers represent dates prior to December 30,
>>1899."
>
>
> Would that not correctly be: December 31, 1899?

In VBA, that would be true only if you consider zero to be a negative number.
--ron