Convert date to serial number

[callbr549]

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

 

[Mike H]

Try

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

With a date in A1

Mike

 

[Mike H]

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

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

Miuke

 

[Jacob Skaria]

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

 

[callbr549]

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

 

[Dave Peterson]

msgbox clng(date) & 1

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

 

[callbr549]

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]

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]

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]

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.

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.

 

[callbr549]

I put most of the code in a separate sub:

Sub Troubleshoot1()
valDate = Calendar1.Value
dateserial = CLng(valDate)
DateAndShift = dateserial & Shift
MsgBox "datecode with shift is " & DateAndShift
End Sub

When this runs, the MsgBox gives the date and shift code in the format I
want (for instance 398961 for Mar 24, first shift). However when I put the
line:

Cells(nextrow, 14) = DateAndShift
in immediately after calling Troubleshoot1, it gets put into the spreadsheet
in format 3/24/20091. If I change column number in this line to 13 instead
of 14, it goes into the spreadsheet in the format I want. I've tried
formating column 14 as number, no difference. I'm stumped.

 

[callbr549]

I found a line I had forgotten to delete later in the sub that was causing
the problem. Your "skinny down your code" advice was what I needed to hear.
Thanks again for all your help

 

[Dave Peterson]

What's the numberformat for that cell that gets the value?

I can't think of any that would change 398961 to a date followed by a number,
but you may want to try:

with cells(Nextrow,14)
.numberformat = "General"
.value = dateandshift
end with

======
I would try declaring all my variables, too.

I put most of the code in a separate sub:

Sub Troubleshoot1()
valDate = Calendar1.Value
dateserial = CLng(valDate)
DateAndShift = dateserial & Shift
MsgBox "datecode with shift is " & DateAndShift
End Sub

When this runs, the MsgBox gives the date and shift code in the format I
want (for instance 398961 for Mar 24, first shift). However when I put the
line:

Cells(nextrow, 14) = DateAndShift
in immediately after calling Troubleshoot1, it gets put into the spreadsheet
in format 3/24/20091. If I change column number in this line to 13 instead
of 14, it goes into the spreadsheet in the format I want. I've tried
formating column 14 as number, no difference. I'm stumped.