date returning time in vba intermediate window
 

The following is an example of some code that I am using in a process
to make charts. While troubleshooting a date calculation error, I
found out that "lastdate" returns a value of 12:00:00 AM in the
intermediate window.
Lastdate is dim'd as public lastdate as date.

ans = MsgBox("Do you want to lock in a review period end date?",
vbYesNo)
If ans = vbYes Then 'lock in a date for the end of review period for
subsequent runs
LastDate = InputBox("What date (MM/DD/YYYY) will mark the end of
the review periods?", , "10/1/2006")
Else: LastDate = CDate(Now()) 'not sure what CDATE does
End If

What am I doing wrong that would cause this to indicate a time instead
of a date as I entered?

TIA

date returning time in vba intermediate window
 

papa jonah wrote:
The following is an example of some code that I am using in a process
to make charts. While troubleshooting a date calculation error, I
found out that "lastdate" returns a value of 12:00:00 AM in the
intermediate window.
Lastdate is dim'd as public lastdate as date.

ans = MsgBox("Do you want to lock in a review period end date?",
vbYesNo)
If ans = vbYes Then 'lock in a date for the end of review period for
subsequent runs
LastDate = InputBox("What date (MM/DD/YYYY) will mark the end of
the review periods?", , "10/1/2006")
Else: LastDate = CDate(Now()) 'not sure what CDATE does
End If

What am I doing wrong that would cause this to indicate a time instead
of a date as I entered?

PJ

If the "date" entered is zero (presumably from cancelling the input box),
then the variable will show the time. Put this in the immediate window:

?clng(lastdate)
0
?format(lastdate,"mm/dd/yyyy")
12/30/1899

Why a date variable would show 12:00 AM instead of 12/30/1899 is a mystery
to me. You probably need to add some code that makes sure the user doesn't
cancel the input box.

--
Dick Kusleika
MVP-Excel
www.dailydoseofexcel.com

date returning time in vba intermediate window
 

I haven't tried your troubleshooting yet, but this occurs without
cancelling the input box.

date returning time in vba intermediate window
 

papa jonah wrote:
I haven't tried your troubleshooting yet, but this occurs without
cancelling the input box.

I pasted your code into a module and ran it, like this

Sub test()

Dim ans As Long
Dim LastDate As Date

ans = MsgBox("Do you want to lock in a review period end date?", vbYesNo)
If ans = vbYes Then 'lock in a date for the end of review period for
subsequent runs
LastDate = InputBox("What date (MM/DD/YYYY) will mark the end of the
review periods?", , "10/1/2006")
Else
LastDate = CDate(Now()) 'not sure what CDATE does
End If
Stop

End Sub

At "Stop", I went to the Immediate Window and typed ?lastdate. The only way
I could get it to return 12:00 AM was to enter zero in the input box. If I
put anything resembling a date, or any other integer, the immediate window
returned a date. If I put in text, I got a type mismatch. Somehow, you're
getting a zero for LastDate and it has to be related to what you're entering
in the input box. What have you entered that gives you that result?
Everything? What version of Excel are you using?


--
Dick Kusleika
MVP-Excel
www.dailydoseofexcel.com