Home |
Search |
Today's Posts |
#41
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert the date to day of the week.
Dave,
I am going to stop working on this right at the moment. Clean up the code and send it to my friend and see if he finds any date problems. I think there will be a problem for Dec months. I also think that when the date is in the General format it works. I can not get the DateSerial to parse correctly but I can address that later. Thank you for all your effort and assistance. You have been very good about helping me and I appreciate it very much. I hope when I come back with some more things on this you will help me. Regards, "Dave Peterson" wrote: If you run it more than one time, the original value was replaced with a date. Nils Titley wrote: RunDate(Beat) = ActiveCell.Offset(0, 1).Value Dim testDate As Date With ActiveCell.Offset(0, 1) MsgBox ActiveCell.Value testDate = DateSerial(Right(.Value, 4), Mid(.Value, 3, 2), Left(.Value, 2)) .NumberFormat = "dd/mm/yy" .Value = testDate End With Msgbox showed : 1 how can that be should the value be a date? Thanks "Dave Peterson" wrote: You can use code like this to change 25022008 to a date: Dim myCell As Range Dim myDate As Date Set myCell = Somerangehere With myCell myDate = DateSerial(Right(.Value, 4), Mid(.Value, 3, 2), Left(.Value, 2)) .NumberFormat = "mmmm dd, yyyy" .Value = myDate End With Nils Titley wrote: Dave, I have been thinking that it might help if I tell you more about the process. Each file will contain 1200 to 2000 rows of data. 10% of the data will not be used. The data lists the date of pickup, the truck that made the pickup, the lat and long and some other informtion. There will be in most cases three runs per file. As I parse through each row in the worksheet, I am totaling # of pickups, meters between pickups and the total number of bins picked up and other data. After I parse all the rows, I generate three rows to the report worksheet - one row for each run. So in reality, I only have to convert three dates from the numerical format to a date format. I have not tried the routine you provided earlier as of yet. But I have an additional question. Since the date is a numerical value, could I store that value in a Long veriable and convert to the date just before I print it since that is the only time I need it in that form and it is the only piece of data that is giving me a hard time. It seems like it would save processing and 3 out of 2000. What do you think? Thanks "Dave Peterson" wrote: Where did you get MyNum? Nils Titley wrote: Ron, I confess, I don't know how to get the numeric value of it? "Ron Rosenfeld" wrote: On Sun, 23 Mar 2008 00:52:04 -0700, Nils Titley wrote: Thanks to all for responding but I am wondering. Will I have a problem with 12/02/08. This is not December 02,08 but it is Feb 12, 08. The dd/mm/yy has already caused a problem. The macro is being used in South Africa. I had to force format on a date because it was changing the format. The code below produced 02/12/08 when it is 12/02/08. It worked only after I changed below to Format(RunDate(MyNum), "dd/mm/yy") With ActiveCell.Offset(0, 0) .NumberFormat = "dd/mm/yy" .Value = RunDate(MyNum) .HorizontalAlignment = xlCenter How do I force the day of the week? Thanks , I hope I am making sense. I believe you may be misunderstanding how dates are being entered into Excel. Excel stores dates as serial numbers with 1 = 1 Jan 1900. It then formats that result depending on the cell format. However, a date expressed like 12/02/08 is ambiguous. So when you enter that value into a cell, Excel has to decide if you mean 12 Feb or 2 Dec. It does this by looking at the date settings -- NOT in Excel -- but at Control Panel/Regional and Language Options. So if your code is producing 2 Dec when you expect it to be producing 12 Feb, you need to look at the output of RunDate(MyNum). If the numeric value is 39490, then it is returning 12 Feb; if 39784, then 2 Dec. If it is returning the desired date, then .numberformat = "dddd" will return the correct day of the week. --ron -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#42
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert the date to day of the week.
Ahhh.
It could be because not all those numbers will be 8 digits (if the day is before the 10th!). dim myStr as string dim myDate as date with activecell mystr = format(.value, "00000000") 'add leading 0's if they're not there myDate = DateSerial(Right(mystr, 4), Mid(mystr, 3, 2), Left(mystr, 2)) .NumberFormat = "mmmm dd, yyyy" .Value = myDate end with ===== There could be other problems, too. If this doesn't help, make sure you post back with the offending string. Nils Titley wrote: Dave, I am going to stop working on this right at the moment. Clean up the code and send it to my friend and see if he finds any date problems. I think there will be a problem for Dec months. I also think that when the date is in the General format it works. I can not get the DateSerial to parse correctly but I can address that later. Thank you for all your effort and assistance. You have been very good about helping me and I appreciate it very much. I hope when I come back with some more things on this you will help me. Regards, "Dave Peterson" wrote: If you run it more than one time, the original value was replaced with a date. Nils Titley wrote: RunDate(Beat) = ActiveCell.Offset(0, 1).Value Dim testDate As Date With ActiveCell.Offset(0, 1) MsgBox ActiveCell.Value testDate = DateSerial(Right(.Value, 4), Mid(.Value, 3, 2), Left(.Value, 2)) .NumberFormat = "dd/mm/yy" .Value = testDate End With Msgbox showed : 1 how can that be should the value be a date? Thanks "Dave Peterson" wrote: You can use code like this to change 25022008 to a date: Dim myCell As Range Dim myDate As Date Set myCell = Somerangehere With myCell myDate = DateSerial(Right(.Value, 4), Mid(.Value, 3, 2), Left(.Value, 2)) .NumberFormat = "mmmm dd, yyyy" .Value = myDate End With Nils Titley wrote: Dave, I have been thinking that it might help if I tell you more about the process. Each file will contain 1200 to 2000 rows of data. 10% of the data will not be used. The data lists the date of pickup, the truck that made the pickup, the lat and long and some other informtion. There will be in most cases three runs per file. As I parse through each row in the worksheet, I am totaling # of pickups, meters between pickups and the total number of bins picked up and other data. After I parse all the rows, I generate three rows to the report worksheet - one row for each run. So in reality, I only have to convert three dates from the numerical format to a date format. I have not tried the routine you provided earlier as of yet. But I have an additional question. Since the date is a numerical value, could I store that value in a Long veriable and convert to the date just before I print it since that is the only time I need it in that form and it is the only piece of data that is giving me a hard time. It seems like it would save processing and 3 out of 2000. What do you think? Thanks "Dave Peterson" wrote: Where did you get MyNum? Nils Titley wrote: Ron, I confess, I don't know how to get the numeric value of it? "Ron Rosenfeld" wrote: On Sun, 23 Mar 2008 00:52:04 -0700, Nils Titley wrote: Thanks to all for responding but I am wondering. Will I have a problem with 12/02/08. This is not December 02,08 but it is Feb 12, 08. The dd/mm/yy has already caused a problem. The macro is being used in South Africa. I had to force format on a date because it was changing the format. The code below produced 02/12/08 when it is 12/02/08. It worked only after I changed below to Format(RunDate(MyNum), "dd/mm/yy") With ActiveCell.Offset(0, 0) .NumberFormat = "dd/mm/yy" .Value = RunDate(MyNum) .HorizontalAlignment = xlCenter How do I force the day of the week? Thanks , I hope I am making sense. I believe you may be misunderstanding how dates are being entered into Excel. Excel stores dates as serial numbers with 1 = 1 Jan 1900. It then formats that result depending on the cell format. However, a date expressed like 12/02/08 is ambiguous. So when you enter that value into a cell, Excel has to decide if you mean 12 Feb or 2 Dec. It does this by looking at the date settings -- NOT in Excel -- but at Control Panel/Regional and Language Options. So if your code is producing 2 Dec when you expect it to be producing 12 Feb, you need to look at the output of RunDate(MyNum). If the numeric value is 39490, then it is returning 12 Feb; if 39784, then 2 Dec. If it is returning the desired date, then .numberformat = "dddd" will return the correct day of the week. --ron -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert regular Date to Week Ending or Week Beginning Dates | Excel Discussion (Misc queries) | |||
How do I convert a date to Monday's date within its week? | Excel Worksheet Functions | |||
Convert Week Number to Date | Excel Discussion (Misc queries) | |||
How do I convert a date to a week of the year? | Excel Discussion (Misc queries) | |||
How can I convert a date into a week number... | Excel Worksheet Functions |