Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert date to serial number
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". |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert date to serial number
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". |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert date to serial number
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". |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert date to serial number
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". |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert date to serial number
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". |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert date to serial number
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert date to serial number
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert date to serial number
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert date to serial number
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert date to serial number
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert date to serial number
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. "Dave Peterson" wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert date to serial number
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" wrote: 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert date to serial number
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. callbr549 wrote: 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. "Dave Peterson" wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert date like string to serial number | Excel Programming | |||
convert serial number into date | Excel Discussion (Misc queries) | |||
How do I convert Date serial number to date | Excel Worksheet Functions | |||
How do I convert a serial number to the month, day, and year in E. | Excel Discussion (Misc queries) | |||
Convert date to serial number in VBA | Excel Programming |