How did you declare dateval? As a date?

This worked ok for me:

Dim DateVal As Long
Dim NextRow As Long
Dim ValDate As Date
Dim myShift As Long

myShift = 1

ValDate = DateSerial(2009, 3, 27) 'your calendar control

NextRow = 1
DateVal = CLng(ValDate)
ActiveSheet.Cells(NextRow, 14) = DateVal & myShift



callbr549 wrote:

This isn't working for me either. Here's the code I'm trying to make work:

dateval = CLng(valDate)
Cells(nextrow, 14) = dateval & Shift

...where valDate is a date from a calendar dialog, and Shift is an integer
from 1 to 3. When the code executes, the cell is getting populated with a
number in the format "3/27/20092" where I'm trying to get "398992". A
similar text operation gets done in another part of the spreadsheet with the
formula:

=IF(ISNUMBER(F57),CONCATENATE(B57,D57),"")

where B57 is the date and D57 is the Shift. This formula returns the format
"398992" even though B57 is in format mm/dd/yyyy.

"Dave Peterson" wrote:

msgbox clng(date) & 1



callbr549 wrote:

I want to convert a date value to the serial number for that date and store
it in a string variable for later manipulation. All I can get is the value
stored in a date format, for example "3/27/2009". The spreadsheet I'm
working on uses the Excel Concatenate function to add a suffix to a date, and
the date comes out in serial number format. So for example, adding "1" to
today's date, the resulting cell reads "398991", but when I try to do the
same thing in VBA I can only get the resulting string to be "3/24/20091".

--

Dave Peterson


--

Dave Peterson