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".

Try

x = Format(Range("A1").Value, 0) * 1

With a date in A1

Mike

"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".

Sorry you want it as a string so leave out the *1

x = Format(Range("A1").Value, 0)

Miuke

"Mike H" wrote:

> Try
>
> x = Format(Range("A1").Value, 0) * 1
>
> With a date in A1
>
> Mike
>
> "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".

Use the simple formula N()

A1 = Date (Shortcut to put date:Ctrl + semicolon)
A2 =N(A1) to convert date to serial number
A3 =TEXT(A2,"dd-mm-yyyy") to convert it back to Date. You can provide your
format.

If this post helps click Yes
---------------
Jacob Skaria


"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".

Still doesn't do what I want. In my VBA I added:

dateval = Format(valDate, 0)

where valDate is a date taken from a dialog box calendar. I concatenate with
another variable with value 2, and get the result 3/26/20092, when I'm trying
to get 398982



"Mike H" wrote:

> Sorry you want it as a string so leave out the *1
>
> x = Format(Range("A1").Value, 0)
>
> Miuke
>
> "Mike H" wrote:
>
> > Try
> >
> > x = Format(Range("A1").Value, 0) * 1
> >
> > With a date in A1
> >
> > Mike
> >
> > "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".

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

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
>

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

I forgot to declare dateval. However, when I do declare dateval as long I
still get the same result. I've gone over the steps of the code you posted
that worked and compared them to mine and I can't see a difference. I must be
overlooking something, but haven't found it yet. I will keep looking. Thanks
for your help so far.

"Dave Peterson" wrote:

> 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
>

You may want to skinny down your code in a test procedure to just the portion
that needs to be there.

Get it working there to see that helps with you real procedure. If you can't
get it working, post the version of that simplified code and explain what's
happening for you.

callbr549 wrote:
>
> I forgot to declare dateval. However, when I do declare dateval as long I
> still get the same result. I've gone over the steps of the code you posted
> that worked and compared them to mine and I can't see a difference. I must be
> overlooking something, but haven't found it yet. I will keep looking. Thanks
> for your help so far.
>
> "Dave Peterson" wrote:
>
> > 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
> >

--

Dave Peterson