Date conversion question

[Chet]

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

 

[Rick Rothstein]

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.

 

[Gary''s Student]

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

 

[Ron Rosenfeld]

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

 

[Chet]

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

 

[CellShocked]

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

Would that not correctly be: December 31, 1899?

 

[Ron Rosenfeld]

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