| Page 1 of 2 | 1 | 2 |
|
|
Convert the date to day of the week.
I need to convert the date 22/03/08 which is dd/mm/yy to what day it is which
is Saturday. Is there a function that does that? Or how can I do what I want? Thanks |
Convert the date to day of the week.
Try this
=CHOOSE(WEEKDAY(A1),"Sunday","Monday","Tuesday","W ednesday","Thursday","Friday","Saturday") -- HTH, Barb Reinhardt "Nils Titley" wrote: I need to convert the date 22/03/08 which is dd/mm/yy to what day it is which is Saturday. Is there a function that does that? Or how can I do what I want? Thanks |
Convert the date to day of the week.
On Sat, 22 Mar 2008 17:30:00 -0700, Nils Titley
wrote: I need to convert the date 22/03/08 which is dd/mm/yy to what day it is which is Saturday. Is there a function that does that? Or how can I do what I want? Thanks You can format the cell (Format/Cells/Number/Custom Type: "dddd") or you can use a formula: =TEXT(cell_ref,"dddd") --ron |
Convert the date to day of the week.
If you just want to see the Saturday, format as dddd
If you want to actually convert, see other replies. Gord Dibben MS Excel MVP On Sat, 22 Mar 2008 17:30:00 -0700, Nils Titley wrote: I need to convert the date 22/03/08 which is dd/mm/yy to what day it is which is Saturday. Is there a function that does that? Or how can I do what I want? Thanks |
Convert the date to day of the week.
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. "Nils Titley" wrote: I need to convert the date 22/03/08 which is dd/mm/yy to what day it is which is Saturday. Is there a function that does that? Or how can I do what I want? Thanks |
Convert the date to day of the week.
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 |
Convert the date to day of the week.
Ron,
Okay, but will setting the international xlDateOrder help? And how do I do that in my Macro. "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 |
Convert the date to day of the week.
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 |
Convert the date to day of the week.
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 |
Convert the date to day of the week.
RunDate = Dim RunDate(0 To 25) As Date
MyNum = Dim MyNum As Long You need more? "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 |
Convert the date to day of the week.
On Sun, 23 Mar 2008 05:14:00 -0700, Nils Titley
wrote: Ron, I confess, I don't know how to get the numeric value of it? Perhaps this will help: ===================================== Option Explicit Sub foo() Const dt As Date = #2/12/2008# Const dt2 As Date = #12/2/2008# Debug.Print CLng(dt), Format(dt, "dddd, dd-mmm-yyyy") Debug.Print CLng(dt2), Format(dt2, "dddd, dd-mmm-yyyy") End Sub ==================================== --ron |
Convert the date to day of the week.
On Sun, 23 Mar 2008 05:09:00 -0700, Nils Titley
wrote: Ron, Okay, but will setting the international xlDateOrder help? And how do I do that in my Macro. As I wrote before, you need to determine what your input is and insure that RunDate(MyNum) is returning the proper date. Excel can usually convert between one date format and another, but you need to be certain that your RunDate function is returning an unambiguous date. --ron |
Convert the date to day of the week.
Yep.
Does the user type it in? Do you pick it up from a cell? Do you import it from a text file? Nils Titley wrote: RunDate = Dim RunDate(0 To 25) As Date MyNum = Dim MyNum As Long You need more? "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 |
Convert the date to day of the week.
Ron,
I am not sure this is helping.... I changed what you have because I don't have #2/12/2008# I have "02/12/2008". When I do that I get debug print A) 02/12/2008 = 39490 120208 (ddmmyy) B) 12/02/2008 = 39784 021208 (ddmmyy) A) ActiveCell.Offset(0, 17).Value = Format(CLng(dt), "ddmmyy") B) ActiveCell.Offset(0, 18).Value = Format(CLng(dt2), "ddmmyy") Results A) 120208 B) 21208 I tried, what I call casting, RunDate(MyNum) as CLng(RunDate(MyNum)) ActiveCell.Offset(0, 0).Value = Format(CLng(RunDate(MyNum)), "ddmmyy") Data = 12/02/2008 Result = 21208 Expecte as above 120208 Please, I don't get it and don't know what to do. Thanks "Ron Rosenfeld" wrote: On Sun, 23 Mar 2008 05:14:00 -0700, Nils Titley wrote: Ron, I confess, I don't know how to get the numeric value of it? Perhaps this will help: ===================================== Option Explicit Sub foo() Const dt As Date = #2/12/2008# Const dt2 As Date = #12/2/2008# Debug.Print CLng(dt), Format(dt, "dddd, dd-mmm-yyyy") Debug.Print CLng(dt2), Format(dt2, "dddd, dd-mmm-yyyy") End Sub ==================================== --ron |
Convert the date to day of the week.
No response????
Dave Peterson wrote: Yep. Does the user type it in? Do you pick it up from a cell? Do you import it from a text file? Nils Titley wrote: RunDate = Dim RunDate(0 To 25) As Date MyNum = Dim MyNum As Long You need more? "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 |
Convert the date to day of the week.
Dave,
I am sorry. I missed you replay. This is the same macro I have been working on. The data are in CSV excel files. I am taking the data from the file and crunching the numbers and output it to another workbook. The dates are in the 12/2/2008 which is ddmmyyyy. All the dates are in that format. 12/2/2008 is the first problem I have encoutered with the date. It things it is mmddyyyy. Gets loads the variable. RunDate(Beat) = ActiveCell.Offset(0, 1).Value Any other questions? Thanks and I am sorry I missed your question. "Dave Peterson" wrote: No response???? Dave Peterson wrote: Yep. Does the user type it in? Do you pick it up from a cell? Do you import it from a text file? Nils Titley wrote: RunDate = Dim RunDate(0 To 25) As Date MyNum = Dim MyNum As Long You need more? "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 |
Convert the date to day of the week.
If you're dates in the CSV file are in dmy order, then I would rename the
workbook to *.txt. Then I could import the data and specify that field as a date in dmy order. I _think_ that what's happening is that other users have a different data order. The data that could pass for dates in that formatted will be converted to a real date -- but probably not what you want. For instance if the data shows: 12/02/2008 and you bring it in with your shortdate format of dmy, then you'll see a date of Feb 12, 2008. But if I bring it in using my mdy shortdate format, I'll see a date of Dec 2, 2008. The real bad news is that with an ambiguous date format of mm/dd/yyyy, you can't tell if the date is really want the CSV intended. And those "dates" that do not look like dates that match the users short date format (25/02/2008 for instance) will be a real date for you (dmy) = Feb 25, 2008. For me with my mdy order, I'll get a text string of 25/02/2008. It won't be a date. So my recommendation is to not rely on the user's setting. Bring the data in a different way--either by renaming the file as *.txt (so you see the wizard). You could also try: Data|Import external data|get data to see the same text to columns wizard. ==== My real point is just because the value looks like a date doesn't mean that it is a date. And even if it is a date, it may not be what the original data intended. Nils Titley wrote: Dave, I am sorry. I missed you replay. This is the same macro I have been working on. The data are in CSV excel files. I am taking the data from the file and crunching the numbers and output it to another workbook. The dates are in the 12/2/2008 which is ddmmyyyy. All the dates are in that format. 12/2/2008 is the first problem I have encoutered with the date. It things it is mmddyyyy. Gets loads the variable. RunDate(Beat) = ActiveCell.Offset(0, 1).Value Any other questions? Thanks and I am sorry I missed your question. "Dave Peterson" wrote: No response???? Dave Peterson wrote: Yep. Does the user type it in? Do you pick it up from a cell? Do you import it from a text file? Nils Titley wrote: RunDate = Dim RunDate(0 To 25) As Date MyNum = Dim MyNum As Long You need more? "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 |
Convert the date to day of the week.
Dave,
The data is not in the form 25/02/2008 it is 25022008. I am going to have to process 60+ files. Is there a quick way to rename the files to .txt? And the last part to try. I don't get because I could not find columns wizard. There has to be a better way to do a conversion. Do you know anything about xlDateOrder application.international? I don't what else to do or how to move forward. I am almost done with what I need to do and this has set me back. Thanks "Dave Peterson" wrote: If you're dates in the CSV file are in dmy order, then I would rename the workbook to *.txt. Then I could import the data and specify that field as a date in dmy order. I _think_ that what's happening is that other users have a different data order. The data that could pass for dates in that formatted will be converted to a real date -- but probably not what you want. For instance if the data shows: 12/02/2008 and you bring it in with your shortdate format of dmy, then you'll see a date of Feb 12, 2008. But if I bring it in using my mdy shortdate format, I'll see a date of Dec 2, 2008. The real bad news is that with an ambiguous date format of mm/dd/yyyy, you can't tell if the date is really want the CSV intended. And those "dates" that do not look like dates that match the users short date format (25/02/2008 for instance) will be a real date for you (dmy) = Feb 25, 2008. For me with my mdy order, I'll get a text string of 25/02/2008. It won't be a date. So my recommendation is to not rely on the user's setting. Bring the data in a different way--either by renaming the file as *.txt (so you see the wizard). You could also try: Data|Import external data|get data to see the same text to columns wizard. ==== My real point is just because the value looks like a date doesn't mean that it is a date. And even if it is a date, it may not be what the original data intended. Nils Titley wrote: Dave, I am sorry. I missed you replay. This is the same macro I have been working on. The data are in CSV excel files. I am taking the data from the file and crunching the numbers and output it to another workbook. The dates are in the 12/2/2008 which is ddmmyyyy. All the dates are in that format. 12/2/2008 is the first problem I have encoutered with the date. It things it is mmddyyyy. Gets loads the variable. RunDate(Beat) = ActiveCell.Offset(0, 1).Value Any other questions? Thanks and I am sorry I missed your question. "Dave Peterson" wrote: No response???? Dave Peterson wrote: Yep. Does the user type it in? Do you pick it up from a cell? Do you import it from a text file? Nils Titley wrote: RunDate = Dim RunDate(0 To 25) As Date MyNum = Dim MyNum As Long You need more? "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 |
Convert the date to day of the week.
Ok, I found the wizard but I can't do that with 60+ files every day. But I
don't think that is what you mean. "Nils Titley" wrote: Dave, The data is not in the form 25/02/2008 it is 25022008. I am going to have to process 60+ files. Is there a quick way to rename the files to .txt? And the last part to try. I don't get because I could not find columns wizard. There has to be a better way to do a conversion. Do you know anything about xlDateOrder application.international? I don't what else to do or how to move forward. I am almost done with what I need to do and this has set me back. Thanks "Dave Peterson" wrote: If you're dates in the CSV file are in dmy order, then I would rename the workbook to *.txt. Then I could import the data and specify that field as a date in dmy order. I _think_ that what's happening is that other users have a different data order. The data that could pass for dates in that formatted will be converted to a real date -- but probably not what you want. For instance if the data shows: 12/02/2008 and you bring it in with your shortdate format of dmy, then you'll see a date of Feb 12, 2008. But if I bring it in using my mdy shortdate format, I'll see a date of Dec 2, 2008. The real bad news is that with an ambiguous date format of mm/dd/yyyy, you can't tell if the date is really want the CSV intended. And those "dates" that do not look like dates that match the users short date format (25/02/2008 for instance) will be a real date for you (dmy) = Feb 25, 2008. For me with my mdy order, I'll get a text string of 25/02/2008. It won't be a date. So my recommendation is to not rely on the user's setting. Bring the data in a different way--either by renaming the file as *.txt (so you see the wizard). You could also try: Data|Import external data|get data to see the same text to columns wizard. ==== My real point is just because the value looks like a date doesn't mean that it is a date. And even if it is a date, it may not be what the original data intended. Nils Titley wrote: Dave, I am sorry. I missed you replay. This is the same macro I have been working on. The data are in CSV excel files. I am taking the data from the file and crunching the numbers and output it to another workbook. The dates are in the 12/2/2008 which is ddmmyyyy. All the dates are in that format. 12/2/2008 is the first problem I have encoutered with the date. It things it is mmddyyyy. Gets loads the variable. RunDate(Beat) = ActiveCell.Offset(0, 1).Value Any other questions? Thanks and I am sorry I missed your question. "Dave Peterson" wrote: No response???? Dave Peterson wrote: Yep. Does the user type it in? Do you pick it up from a cell? Do you import it from a text file? Nils Titley wrote: RunDate = Dim RunDate(0 To 25) As Date MyNum = Dim MyNum As Long You need more? "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 |
Convert the date to day of the week.
If the value in the CSV file is 25022008, then it's not a date (to excel!).
It's just an 8 digit number. It's up to you to convert it to a date. You could record a macro to select the column and then data|text to columns. You can specify that this field is a date and is in dmy order. After you convert that 8 digit number to a real date, you can format the column in any date format that you want. If you're opening the file in a macro, then you could add this step (for each field that needs it!) right after you open the file. Nils Titley wrote: Dave, The data is not in the form 25/02/2008 it is 25022008. I am going to have to process 60+ files. Is there a quick way to rename the files to .txt? And the last part to try. I don't get because I could not find columns wizard. There has to be a better way to do a conversion. Do you know anything about xlDateOrder application.international? I don't what else to do or how to move forward. I am almost done with what I need to do and this has set me back. Thanks "Dave Peterson" wrote: If you're dates in the CSV file are in dmy order, then I would rename the workbook to *.txt. Then I could import the data and specify that field as a date in dmy order. I _think_ that what's happening is that other users have a different data order. The data that could pass for dates in that formatted will be converted to a real date -- but probably not what you want. For instance if the data shows: 12/02/2008 and you bring it in with your shortdate format of dmy, then you'll see a date of Feb 12, 2008. But if I bring it in using my mdy shortdate format, I'll see a date of Dec 2, 2008. The real bad news is that with an ambiguous date format of mm/dd/yyyy, you can't tell if the date is really want the CSV intended. And those "dates" that do not look like dates that match the users short date format (25/02/2008 for instance) will be a real date for you (dmy) = Feb 25, 2008. For me with my mdy order, I'll get a text string of 25/02/2008. It won't be a date. So my recommendation is to not rely on the user's setting. Bring the data in a different way--either by renaming the file as *.txt (so you see the wizard). You could also try: Data|Import external data|get data to see the same text to columns wizard. ==== My real point is just because the value looks like a date doesn't mean that it is a date. And even if it is a date, it may not be what the original data intended. Nils Titley wrote: Dave, I am sorry. I missed you replay. This is the same macro I have been working on. The data are in CSV excel files. I am taking the data from the file and crunching the numbers and output it to another workbook. The dates are in the 12/2/2008 which is ddmmyyyy. All the dates are in that format. 12/2/2008 is the first problem I have encoutered with the date. It things it is mmddyyyy. Gets loads the variable. RunDate(Beat) = ActiveCell.Offset(0, 1).Value Any other questions? Thanks and I am sorry I missed your question. "Dave Peterson" wrote: No response???? Dave Peterson wrote: Yep. Does the user type it in? Do you pick it up from a cell? Do you import it from a text file? Nils Titley wrote: RunDate = Dim RunDate(0 To 25) As Date MyNum = Dim MyNum As Long You need more? "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 -- Dave Peterson |
Convert the date to day of the week.
Ok,
I see that there is a TextToColumns. Can you help me. Can I read the file and write it back out over it self or do I have to create a new file? "Dave Peterson" wrote: If you're dates in the CSV file are in dmy order, then I would rename the workbook to *.txt. Then I could import the data and specify that field as a date in dmy order. I _think_ that what's happening is that other users have a different data order. The data that could pass for dates in that formatted will be converted to a real date -- but probably not what you want. For instance if the data shows: 12/02/2008 and you bring it in with your shortdate format of dmy, then you'll see a date of Feb 12, 2008. But if I bring it in using my mdy shortdate format, I'll see a date of Dec 2, 2008. The real bad news is that with an ambiguous date format of mm/dd/yyyy, you can't tell if the date is really want the CSV intended. And those "dates" that do not look like dates that match the users short date format (25/02/2008 for instance) will be a real date for you (dmy) = Feb 25, 2008. For me with my mdy order, I'll get a text string of 25/02/2008. It won't be a date. So my recommendation is to not rely on the user's setting. Bring the data in a different way--either by renaming the file as *.txt (so you see the wizard). You could also try: Data|Import external data|get data to see the same text to columns wizard. ==== My real point is just because the value looks like a date doesn't mean that it is a date. And even if it is a date, it may not be what the original data intended. Nils Titley wrote: Dave, I am sorry. I missed you replay. This is the same macro I have been working on. The data are in CSV excel files. I am taking the data from the file and crunching the numbers and output it to another workbook. The dates are in the 12/2/2008 which is ddmmyyyy. All the dates are in that format. 12/2/2008 is the first problem I have encoutered with the date. It things it is mmddyyyy. Gets loads the variable. RunDate(Beat) = ActiveCell.Offset(0, 1).Value Any other questions? Thanks and I am sorry I missed your question. "Dave Peterson" wrote: No response???? Dave Peterson wrote: Yep. Does the user type it in? Do you pick it up from a cell? Do you import it from a text file? Nils Titley wrote: RunDate = Dim RunDate(0 To 25) As Date MyNum = Dim MyNum As Long You need more? "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 |
Convert the date to day of the week.
Ok but isn't there a built in function that will do the conversion without
calling another macro? I am feeling a lot out of my element here. I have run into these problems before but that is when I was programming full time. I am just learning how to do what I want in VB with Excel. Short example? "Dave Peterson" wrote: If the value in the CSV file is 25022008, then it's not a date (to excel!). It's just an 8 digit number. It's up to you to convert it to a date. You could record a macro to select the column and then data|text to columns. You can specify that this field is a date and is in dmy order. After you convert that 8 digit number to a real date, you can format the column in any date format that you want. If you're opening the file in a macro, then you could add this step (for each field that needs it!) right after you open the file. Nils Titley wrote: Dave, The data is not in the form 25/02/2008 it is 25022008. I am going to have to process 60+ files. Is there a quick way to rename the files to .txt? And the last part to try. I don't get because I could not find columns wizard. There has to be a better way to do a conversion. Do you know anything about xlDateOrder application.international? I don't what else to do or how to move forward. I am almost done with what I need to do and this has set me back. Thanks "Dave Peterson" wrote: If you're dates in the CSV file are in dmy order, then I would rename the workbook to *.txt. Then I could import the data and specify that field as a date in dmy order. I _think_ that what's happening is that other users have a different data order. The data that could pass for dates in that formatted will be converted to a real date -- but probably not what you want. For instance if the data shows: 12/02/2008 and you bring it in with your shortdate format of dmy, then you'll see a date of Feb 12, 2008. But if I bring it in using my mdy shortdate format, I'll see a date of Dec 2, 2008. The real bad news is that with an ambiguous date format of mm/dd/yyyy, you can't tell if the date is really want the CSV intended. And those "dates" that do not look like dates that match the users short date format (25/02/2008 for instance) will be a real date for you (dmy) = Feb 25, 2008. For me with my mdy order, I'll get a text string of 25/02/2008. It won't be a date. So my recommendation is to not rely on the user's setting. Bring the data in a different way--either by renaming the file as *.txt (so you see the wizard). You could also try: Data|Import external data|get data to see the same text to columns wizard. ==== My real point is just because the value looks like a date doesn't mean that it is a date. And even if it is a date, it may not be what the original data intended. Nils Titley wrote: Dave, I am sorry. I missed you replay. This is the same macro I have been working on. The data are in CSV excel files. I am taking the data from the file and crunching the numbers and output it to another workbook. The dates are in the 12/2/2008 which is ddmmyyyy. All the dates are in that format. 12/2/2008 is the first problem I have encoutered with the date. It things it is mmddyyyy. Gets loads the variable. RunDate(Beat) = ActiveCell.Offset(0, 1).Value Any other questions? Thanks and I am sorry I missed your question. "Dave Peterson" wrote: No response???? Dave Peterson wrote: Yep. Does the user type it in? Do you pick it up from a cell? Do you import it from a text file? Nils Titley wrote: RunDate = Dim RunDate(0 To 25) As Date MyNum = Dim MyNum As Long You need more? "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 -- Dave Peterson |
Convert the date to day of the week.
The builtin function is data|text to columns. You could use worksheet functions
and parse the string into its parts, but it would require extra cells and would be slower. =date(right(a1,4),mid(a1,3,2),left(a1,2)) I recorded a macro when I selected a column and did the data|text to columns. Then I tweaked the code: Option Explicit Sub testme() Dim myRng As Range With Worksheets("sheet1") Set myRng = .Range("D1").EntireColumn End With With myRng .TextToColumns Destination:=.Cells(1), _ DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, _ Tab:=False, _ Semicolon:=False, _ Comma:=False, _ Space:=False, _ Other:=False, _ FieldInfo:=Array(1, 4) .NumberFormat = "mmmm dd, yyyy" .AutoFit End With End Sub Nils Titley wrote: Ok but isn't there a built in function that will do the conversion without calling another macro? I am feeling a lot out of my element here. I have run into these problems before but that is when I was programming full time. I am just learning how to do what I want in VB with Excel. Short example? "Dave Peterson" wrote: If the value in the CSV file is 25022008, then it's not a date (to excel!). It's just an 8 digit number. It's up to you to convert it to a date. You could record a macro to select the column and then data|text to columns. You can specify that this field is a date and is in dmy order. After you convert that 8 digit number to a real date, you can format the column in any date format that you want. If you're opening the file in a macro, then you could add this step (for each field that needs it!) right after you open the file. Nils Titley wrote: Dave, The data is not in the form 25/02/2008 it is 25022008. I am going to have to process 60+ files. Is there a quick way to rename the files to .txt? And the last part to try. I don't get because I could not find columns wizard. There has to be a better way to do a conversion. Do you know anything about xlDateOrder application.international? I don't what else to do or how to move forward. I am almost done with what I need to do and this has set me back. Thanks "Dave Peterson" wrote: If you're dates in the CSV file are in dmy order, then I would rename the workbook to *.txt. Then I could import the data and specify that field as a date in dmy order. I _think_ that what's happening is that other users have a different data order. The data that could pass for dates in that formatted will be converted to a real date -- but probably not what you want. For instance if the data shows: 12/02/2008 and you bring it in with your shortdate format of dmy, then you'll see a date of Feb 12, 2008. But if I bring it in using my mdy shortdate format, I'll see a date of Dec 2, 2008. The real bad news is that with an ambiguous date format of mm/dd/yyyy, you can't tell if the date is really want the CSV intended. And those "dates" that do not look like dates that match the users short date format (25/02/2008 for instance) will be a real date for you (dmy) = Feb 25, 2008. For me with my mdy order, I'll get a text string of 25/02/2008. It won't be a date. So my recommendation is to not rely on the user's setting. Bring the data in a different way--either by renaming the file as *.txt (so you see the wizard). You could also try: Data|Import external data|get data to see the same text to columns wizard. ==== My real point is just because the value looks like a date doesn't mean that it is a date. And even if it is a date, it may not be what the original data intended. Nils Titley wrote: Dave, I am sorry. I missed you replay. This is the same macro I have been working on. The data are in CSV excel files. I am taking the data from the file and crunching the numbers and output it to another workbook. The dates are in the 12/2/2008 which is ddmmyyyy. All the dates are in that format. 12/2/2008 is the first problem I have encoutered with the date. It things it is mmddyyyy. Gets loads the variable. RunDate(Beat) = ActiveCell.Offset(0, 1).Value Any other questions? Thanks and I am sorry I missed your question. "Dave Peterson" wrote: No response???? Dave Peterson wrote: Yep. Does the user type it in? Do you pick it up from a cell? Do you import it from a text file? Nils Titley wrote: RunDate = Dim RunDate(0 To 25) As Date MyNum = Dim MyNum As Long You need more? "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 -- Dave Peterson -- Dave Peterson |
Convert the date to day of the week.
Ps.
I still don't know how you're opening the 60 .csv files. But Ron de Bruin has some options he http://www.rondebruin.nl/csv.htm http://www.rondebruin.nl/txtcsv.htm Nils Titley wrote: Ok but isn't there a built in function that will do the conversion without calling another macro? I am feeling a lot out of my element here. I have run into these problems before but that is when I was programming full time. I am just learning how to do what I want in VB with Excel. Short example? "Dave Peterson" wrote: If the value in the CSV file is 25022008, then it's not a date (to excel!). It's just an 8 digit number. It's up to you to convert it to a date. You could record a macro to select the column and then data|text to columns. You can specify that this field is a date and is in dmy order. After you convert that 8 digit number to a real date, you can format the column in any date format that you want. If you're opening the file in a macro, then you could add this step (for each field that needs it!) right after you open the file. Nils Titley wrote: Dave, The data is not in the form 25/02/2008 it is 25022008. I am going to have to process 60+ files. Is there a quick way to rename the files to .txt? And the last part to try. I don't get because I could not find columns wizard. There has to be a better way to do a conversion. Do you know anything about xlDateOrder application.international? I don't what else to do or how to move forward. I am almost done with what I need to do and this has set me back. Thanks "Dave Peterson" wrote: If you're dates in the CSV file are in dmy order, then I would rename the workbook to *.txt. Then I could import the data and specify that field as a date in dmy order. I _think_ that what's happening is that other users have a different data order. The data that could pass for dates in that formatted will be converted to a real date -- but probably not what you want. For instance if the data shows: 12/02/2008 and you bring it in with your shortdate format of dmy, then you'll see a date of Feb 12, 2008. But if I bring it in using my mdy shortdate format, I'll see a date of Dec 2, 2008. The real bad news is that with an ambiguous date format of mm/dd/yyyy, you can't tell if the date is really want the CSV intended. And those "dates" that do not look like dates that match the users short date format (25/02/2008 for instance) will be a real date for you (dmy) = Feb 25, 2008. For me with my mdy order, I'll get a text string of 25/02/2008. It won't be a date. So my recommendation is to not rely on the user's setting. Bring the data in a different way--either by renaming the file as *.txt (so you see the wizard). You could also try: Data|Import external data|get data to see the same text to columns wizard. ==== My real point is just because the value looks like a date doesn't mean that it is a date. And even if it is a date, it may not be what the original data intended. Nils Titley wrote: Dave, I am sorry. I missed you replay. This is the same macro I have been working on. The data are in CSV excel files. I am taking the data from the file and crunching the numbers and output it to another workbook. The dates are in the 12/2/2008 which is ddmmyyyy. All the dates are in that format. 12/2/2008 is the first problem I have encoutered with the date. It things it is mmddyyyy. Gets loads the variable. RunDate(Beat) = ActiveCell.Offset(0, 1).Value Any other questions? Thanks and I am sorry I missed your question. "Dave Peterson" wrote: No response???? Dave Peterson wrote: Yep. Does the user type it in? Do you pick it up from a cell? Do you import it from a text file? Nils Titley wrote: RunDate = Dim RunDate(0 To 25) As Date MyNum = Dim MyNum As Long You need more? "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 -- Dave Peterson -- Dave Peterson |
Convert the date to day of the week.
On Sun, 23 Mar 2008 07:52:00 -0700, Nils Titley
wrote: Ron, I am not sure this is helping.... I changed what you have because I don't have #2/12/2008# I have "02/12/2008". If you did that, in the CONST line, you would have gotten a "type mismatch" error. So I don't understand what you mean here or what you did. When I do that I get debug print A) 02/12/2008 = 39490 120208 (ddmmyy) B) 12/02/2008 = 39784 021208 (ddmmyy) A) ActiveCell.Offset(0, 17).Value = Format(CLng(dt), "ddmmyy") B) ActiveCell.Offset(0, 18).Value = Format(CLng(dt2), "ddmmyy") Results A) 120208 B) 21208 I tried, what I call casting, RunDate(MyNum) as CLng(RunDate(MyNum)) I'm not familiar with your term "casting" in this context. So again, I don't understand what you mean. ActiveCell.Offset(0, 0).Value = Format(CLng(RunDate(MyNum)), "ddmmyy") Data = 12/02/2008 Result = 21208 Expecte as above 120208 Please, I don't get it and don't know what to do. Thanks Let me repeat: "Excel stores dates as serial numbers" If you have a value, expressed as a serial number, of 21208 (or 021208), and then try to express that as a date, it will translate to be 21,208 days after 1 Jan 1900, or Thursday, January 23, 1958. You still haven't indicated what your RunDate function is doing, or what MyNum is. Also, perhaps you do not understand the formatting a cell as a date has AABSOLUTELY NOTHING TO DO with how Excel interprets the value in that cell. It only determines how the value in that cell is displayed. Excel interprets date input according to the settings/format in the REGIONAL AND LANGUAGE settings (in Control Panel). Usually, that format defines the order (dmy, mdy, etc) as well as the separator (usually a "/"). If your data input does not conform to these specifications (i.e. if your data is 120208 instead of 12/02/08), you will need to manipulate the data in order to correct this. If you could answer the questions I've posed, it should be possible to help. --ron |
Convert the date to day of the week.
Dave
I open them one of the time after I have gotten all the files names. I read a file, process that file print the data to a worksheet.. continue ' Fill the array (myFiles) with the list of Excel files in the folder NumFiles = 0 Do While FilesInPath < "" ' Dynamically size MyFiles based on number of files ReDim Preserve MyFiles(0 To NumFiles) MyFiles(NumFiles) = FilesInPath FilesInPath = Dir() If (FilesInPath < "") Then NumFiles = NumFiles + 1 End If Loop Set WBNew = Workbooks.Add ' Change ScreenUpdating, Calculation and EnableEvents With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With ' Loop through the files to process them NumDone = 0 Do While (NumDone <= NumFiles) Set FileBook = Workbooks.Open(MyPath & MyFiles(NumDone)) Does that help. "Dave Peterson" wrote: Ps. I still don't know how you're opening the 60 .csv files. But Ron de Bruin has some options he http://www.rondebruin.nl/csv.htm http://www.rondebruin.nl/txtcsv.htm Nils Titley wrote: Ok but isn't there a built in function that will do the conversion without calling another macro? I am feeling a lot out of my element here. I have run into these problems before but that is when I was programming full time. I am just learning how to do what I want in VB with Excel. Short example? "Dave Peterson" wrote: If the value in the CSV file is 25022008, then it's not a date (to excel!). It's just an 8 digit number. It's up to you to convert it to a date. You could record a macro to select the column and then data|text to columns. You can specify that this field is a date and is in dmy order. After you convert that 8 digit number to a real date, you can format the column in any date format that you want. If you're opening the file in a macro, then you could add this step (for each field that needs it!) right after you open the file. Nils Titley wrote: Dave, The data is not in the form 25/02/2008 it is 25022008. I am going to have to process 60+ files. Is there a quick way to rename the files to .txt? And the last part to try. I don't get because I could not find columns wizard. There has to be a better way to do a conversion. Do you know anything about xlDateOrder application.international? I don't what else to do or how to move forward. I am almost done with what I need to do and this has set me back. Thanks "Dave Peterson" wrote: If you're dates in the CSV file are in dmy order, then I would rename the workbook to *.txt. Then I could import the data and specify that field as a date in dmy order. I _think_ that what's happening is that other users have a different data order. The data that could pass for dates in that formatted will be converted to a real date -- but probably not what you want. For instance if the data shows: 12/02/2008 and you bring it in with your shortdate format of dmy, then you'll see a date of Feb 12, 2008. But if I bring it in using my mdy shortdate format, I'll see a date of Dec 2, 2008. The real bad news is that with an ambiguous date format of mm/dd/yyyy, you can't tell if the date is really want the CSV intended. And those "dates" that do not look like dates that match the users short date format (25/02/2008 for instance) will be a real date for you (dmy) = Feb 25, 2008. For me with my mdy order, I'll get a text string of 25/02/2008. It won't be a date. So my recommendation is to not rely on the user's setting. Bring the data in a different way--either by renaming the file as *.txt (so you see the wizard). You could also try: Data|Import external data|get data to see the same text to columns wizard. ==== My real point is just because the value looks like a date doesn't mean that it is a date. And even if it is a date, it may not be what the original data intended. Nils Titley wrote: Dave, I am sorry. I missed you replay. This is the same macro I have been working on. The data are in CSV excel files. I am taking the data from the file and crunching the numbers and output it to another workbook. The dates are in the 12/2/2008 which is ddmmyyyy. All the dates are in that format. 12/2/2008 is the first problem I have encoutered with the date. It things it is mmddyyyy. Gets loads the variable. RunDate(Beat) = ActiveCell.Offset(0, 1).Value Any other questions? Thanks and I am sorry I missed your question. "Dave Peterson" wrote: No response???? Dave Peterson wrote: Yep. Does the user type it in? Do you pick it up from a cell? Do you import it from a text file? Nils Titley wrote: RunDate = Dim RunDate(0 To 25) As Date MyNum = Dim MyNum As Long You need more? "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 -- Dave Peterson -- Dave Peterson |
Convert the date to day of the week.
In C you change the type to another type - my be my memory is wrong but I
believe the term is called casting "Ron Rosenfeld" wrote: On Sun, 23 Mar 2008 07:52:00 -0700, Nils Titley wrote: Ron, I am not sure this is helping.... I changed what you have because I don't have #2/12/2008# I have "02/12/2008". If you did that, in the CONST line, you would have gotten a "type mismatch" error. So I don't understand what you mean here or what you did. When I do that I get debug print A) 02/12/2008 = 39490 120208 (ddmmyy) B) 12/02/2008 = 39784 021208 (ddmmyy) A) ActiveCell.Offset(0, 17).Value = Format(CLng(dt), "ddmmyy") B) ActiveCell.Offset(0, 18).Value = Format(CLng(dt2), "ddmmyy") Results A) 120208 B) 21208 I tried, what I call casting, RunDate(MyNum) as CLng(RunDate(MyNum)) I'm not familiar with your term "casting" in this context. So again, I don't understand what you mean. ActiveCell.Offset(0, 0).Value = Format(CLng(RunDate(MyNum)), "ddmmyy") Data = 12/02/2008 Result = 21208 Expecte as above 120208 Please, I don't get it and don't know what to do. Thanks Let me repeat: "Excel stores dates as serial numbers" If you have a value, expressed as a serial number, of 21208 (or 021208), and then try to express that as a date, it will translate to be 21,208 days after 1 Jan 1900, or Thursday, January 23, 1958. You still haven't indicated what your RunDate function is doing, or what MyNum is. Also, perhaps you do not understand the formatting a cell as a date has AABSOLUTELY NOTHING TO DO with how Excel interprets the value in that cell. It only determines how the value in that cell is displayed. Excel interprets date input according to the settings/format in the REGIONAL AND LANGUAGE settings (in Control Panel). Usually, that format defines the order (dmy, mdy, etc) as well as the separator (usually a "/"). If your data input does not conform to these specifications (i.e. if your data is 120208 instead of 12/02/08), you will need to manipulate the data in order to correct this. If you could answer the questions I've posed, it should be possible to help. --ron |
Convert the date to day of the week.
So you're opening the .CSV files via a macro.
You can add the code to convert the numbers to dates right after you open the ..CSV file. Nils Titley wrote: Dave I open them one of the time after I have gotten all the files names. I read a file, process that file print the data to a worksheet.. continue ' Fill the array (myFiles) with the list of Excel files in the folder NumFiles = 0 Do While FilesInPath < "" ' Dynamically size MyFiles based on number of files ReDim Preserve MyFiles(0 To NumFiles) MyFiles(NumFiles) = FilesInPath FilesInPath = Dir() If (FilesInPath < "") Then NumFiles = NumFiles + 1 End If Loop Set WBNew = Workbooks.Add ' Change ScreenUpdating, Calculation and EnableEvents With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With ' Loop through the files to process them NumDone = 0 Do While (NumDone <= NumFiles) Set FileBook = Workbooks.Open(MyPath & MyFiles(NumDone)) Does that help. "Dave Peterson" wrote: Ps. I still don't know how you're opening the 60 .csv files. But Ron de Bruin has some options he http://www.rondebruin.nl/csv.htm http://www.rondebruin.nl/txtcsv.htm Nils Titley wrote: Ok but isn't there a built in function that will do the conversion without calling another macro? I am feeling a lot out of my element here. I have run into these problems before but that is when I was programming full time. I am just learning how to do what I want in VB with Excel. Short example? "Dave Peterson" wrote: If the value in the CSV file is 25022008, then it's not a date (to excel!). It's just an 8 digit number. It's up to you to convert it to a date. You could record a macro to select the column and then data|text to columns. You can specify that this field is a date and is in dmy order. After you convert that 8 digit number to a real date, you can format the column in any date format that you want. If you're opening the file in a macro, then you could add this step (for each field that needs it!) right after you open the file. Nils Titley wrote: Dave, The data is not in the form 25/02/2008 it is 25022008. I am going to have to process 60+ files. Is there a quick way to rename the files to .txt? And the last part to try. I don't get because I could not find columns wizard. There has to be a better way to do a conversion. Do you know anything about xlDateOrder application.international? I don't what else to do or how to move forward. I am almost done with what I need to do and this has set me back. Thanks "Dave Peterson" wrote: If you're dates in the CSV file are in dmy order, then I would rename the workbook to *.txt. Then I could import the data and specify that field as a date in dmy order. I _think_ that what's happening is that other users have a different data order. The data that could pass for dates in that formatted will be converted to a real date -- but probably not what you want. For instance if the data shows: 12/02/2008 and you bring it in with your shortdate format of dmy, then you'll see a date of Feb 12, 2008. But if I bring it in using my mdy shortdate format, I'll see a date of Dec 2, 2008. The real bad news is that with an ambiguous date format of mm/dd/yyyy, you can't tell if the date is really want the CSV intended. And those "dates" that do not look like dates that match the users short date format (25/02/2008 for instance) will be a real date for you (dmy) = Feb 25, 2008. For me with my mdy order, I'll get a text string of 25/02/2008. It won't be a date. So my recommendation is to not rely on the user's setting. Bring the data in a different way--either by renaming the file as *.txt (so you see the wizard). You could also try: Data|Import external data|get data to see the same text to columns wizard. ==== My real point is just because the value looks like a date doesn't mean that it is a date. And even if it is a date, it may not be what the original data intended. Nils Titley wrote: Dave, I am sorry. I missed you replay. This is the same macro I have been working on. The data are in CSV excel files. I am taking the data from the file and crunching the numbers and output it to another workbook. The dates are in the 12/2/2008 which is ddmmyyyy. All the dates are in that format. 12/2/2008 is the first problem I have encoutered with the date. It things it is mmddyyyy. Gets loads the variable. RunDate(Beat) = ActiveCell.Offset(0, 1).Value Any other questions? Thanks and I am sorry I missed your question. "Dave Peterson" wrote: No response???? Dave Peterson wrote: Yep. Does the user type it in? Do you pick it up from a cell? Do you import it from a text file? Nils Titley wrote: RunDate = Dim RunDate(0 To 25) As Date MyNum = Dim MyNum As Long You need more? "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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Convert the date to day of the week.
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 |
Convert the date to day of the week.
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 |
Convert the date to day of the week.
Dave
Thanks for the code. I will give it a try. I am doing some more analysis of the data. I may be running into user changing the data and so it is not in its raw form. When it is raw the date value is not in a date form but in a general form. I am going to add your code to see what happens. I leave another message when I know some thing. Dave, I appreciate the help you are giving me and the other suggestions you have made. You have been a big help to me. "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 |
Convert the date to day of the week.
Nils - with the regional date settings you've described, and the
imported "date" in cell a2, in a cell formatted "general" the formula below will return the NUMBER that represents the date for the ddmmyyyy format you've described. Your example of 25022008 would appear as 39503 representing February 25, 2008. =VALUE(CONCATENATE(MID(a2,3,2),"/",LEFT(a2,2),"/",RIGHT(a2,2))) Then you can FORMAT it as a date. Cliff Edwards |
Convert the date to day of the week.
Dave
I did this: Dim testDate As Date With ActiveCell.Offset(0, 0) testDate = DateSerial(Right(.Value, 4), Mid(.Value, 3, 2), Left(.Value, 2)) .NumberFormat = "dd/mm/yy" .Value = testDate End With But I got a run time 13 error on the testDate line. What am I actually doing with Right (.Value, 4) is that for the year, Mid (.value, 3, 2) is that for the day and Left .... for the month. Why did it stop. Can't I access the cell the way I did it? Thanks "Nils Titley" wrote: Dave Thanks for the code. I will give it a try. I am doing some more analysis of the data. I may be running into user changing the data and so it is not in its raw form. When it is raw the date value is not in a date form but in a general form. I am going to add your code to see what happens. I leave another message when I know some thing. Dave, I appreciate the help you are giving me and the other suggestions you have made. You have been a big help to me. "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 |
Convert the date to day of the week.
I'm guessing that the value in the Activecell isn't what you expected.
Add a line: msgbox Activecell.value before the line that causes the error. ps. Although this works: Activecell.offset(0,0) the .offset(0,0) doesn't really do much. Why not just use Activecell? Nils Titley wrote: Dave I did this: Dim testDate As Date With ActiveCell.Offset(0, 0) testDate = DateSerial(Right(.Value, 4), Mid(.Value, 3, 2), Left(.Value, 2)) .NumberFormat = "dd/mm/yy" .Value = testDate End With But I got a run time 13 error on the testDate line. What am I actually doing with Right (.Value, 4) is that for the year, Mid (.value, 3, 2) is that for the day and Left .... for the month. Why did it stop. Can't I access the cell the way I did it? Thanks "Nils Titley" wrote: Dave Thanks for the code. I will give it a try. I am doing some more analysis of the data. I may be running into user changing the data and so it is not in its raw form. When it is raw the date value is not in a date form but in a general form. I am going to add your code to see what happens. I leave another message when I know some thing. Dave, I appreciate the help you are giving me and the other suggestions you have made. You have been a big help to me. "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 |
Convert the date to day of the week.
I am going to add more. It had a problem because it doesn't have a value to work on. I am having a little trouble seeing how this works. For example RunDate(Beat) = ActiveCell.Offset(0, 1).Value This takes the value and loads the variable. ActiveCell.Offset(0, 0).Value = Format((RunDate(MyNum)), "dd/mm/yy") This line loads the date into the cell I want to store the date. At what point do I use your new code and I don't see how to load the variable or the value. Please exaplain more... Thanks sorry I don't see it. "Nils Titley" wrote: Dave I did this: Dim testDate As Date With ActiveCell.Offset(0, 0) testDate = DateSerial(Right(.Value, 4), Mid(.Value, 3, 2), Left(.Value, 2)) .NumberFormat = "dd/mm/yy" .Value = testDate End With But I got a run time 13 error on the testDate line. What am I actually doing with Right (.Value, 4) is that for the year, Mid (.value, 3, 2) is that for the day and Left .... for the month. Why did it stop. Can't I access the cell the way I did it? Thanks "Nils Titley" wrote: Dave Thanks for the code. I will give it a try. I am doing some more analysis of the data. I may be running into user changing the data and so it is not in its raw form. When it is raw the date value is not in a date form but in a general form. I am going to add your code to see what happens. I leave another message when I know some thing. Dave, I appreciate the help you are giving me and the other suggestions you have made. You have been a big help to me. "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 |
Convert the date to day of the week.
I don't understand what rundate does. It looks like it just holds a number and
doesn't do anything to convert that string of digits into a real date. dim myDate as date with activecell if isempty(.value) then 'what happens if it's empty??? else myDate = DateSerial(Right(.Value, 4), Mid(.Value, 3, 2), Left(.Value, 2)) .numberformat = "mmmm dd, yyyy" .value = mydate end if end with Nils Titley wrote: I am going to add more. It had a problem because it doesn't have a value to work on. I am having a little trouble seeing how this works. For example RunDate(Beat) = ActiveCell.Offset(0, 1).Value This takes the value and loads the variable. ActiveCell.Offset(0, 0).Value = Format((RunDate(MyNum)), "dd/mm/yy") This line loads the date into the cell I want to store the date. At what point do I use your new code and I don't see how to load the variable or the value. Please exaplain more... Thanks sorry I don't see it. "Nils Titley" wrote: Dave I did this: Dim testDate As Date With ActiveCell.Offset(0, 0) testDate = DateSerial(Right(.Value, 4), Mid(.Value, 3, 2), Left(.Value, 2)) .NumberFormat = "dd/mm/yy" .Value = testDate End With But I got a run time 13 error on the testDate line. What am I actually doing with Right (.Value, 4) is that for the year, Mid (.value, 3, 2) is that for the day and Left .... for the month. Why did it stop. Can't I access the cell the way I did it? Thanks "Nils Titley" wrote: Dave Thanks for the code. I will give it a try. I am doing some more analysis of the data. I may be running into user changing the data and so it is not in its raw form. When it is raw the date value is not in a date form but in a general form. I am going to add your code to see what happens. I leave another message when I know some thing. Dave, I appreciate the help you are giving me and the other suggestions you have made. You have been a big help to me. "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 |
Convert the date to day of the week.
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 |
Convert the date to day of the week.
David,
I made an interesting discovery about the data. When I look at the format of the date in the CVS file it is in General format as a date. If I try putting a date in it is in date format. If I change a date to changes it to the date number. How do I format the whole column in the general format? I still want to change the numbers but I can't get what you provided to work. "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 |
Convert the date to day of the week.
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 |
Convert the date to day of the week.
Try recording a macro when you select a column and change the format.
I don't have another guess why you're having trouble. You'll have to provide more details of what you tried. Nils Titley wrote: David, I made an interesting discovery about the data. When I look at the format of the date in the CVS file it is in General format as a date. If I try putting a date in it is in date format. If I change a date to changes it to the date number. How do I format the whole column in the general format? I still want to change the numbers but I can't get what you provided to work. "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 |
| All times are GMT +1. The time now is 04:00 PM. |
|
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com