Hi Kiwis
I have similar experience. If the data is from import from a CSV/text file,
not typed and the date format is not the same as the one you are using
(depends on your locale) then it cause problem.
This problem (#value!) is because while you import the those data, Excel try
to turn it to "date" value but found it successful, e.g. 1/13/07 if the day
format that it recognise is d/m/yy. This happens no matter you have " or
comma as deliminator.
There are 2 approach to solve this problem. 1st, make it m/d/yy during
import by specifying it in the import data macro.
2nd, Do a conversion after that by retrieving 3 values between "/", then
recombine it to a date format. But it require that it is a text and nothing
being convered in the middle.
For approach 1, use below coding:
Sub OpenTextFile()
Workbooks.OpenText Filename:= _
"C:\test.txt", _
DataType:=xlDelimited, comma:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 4))
'<-- 4 means xlDMYFormat
'FieldInfo:=Array(Array(1, 1), Array(2, 3))
'<-- 3 means xlMDYFormat
End Sub
if you use '3' then the data format in text/CSV file you want to import
should be in DMY, else if your data is in MDY, use '4' . Array (2,4) the 2
mean the second field.
This FieldInfo parameter is very useful for importing.
FYI:
1: xlGeneralFormat
2: xlText
3: xlMDYFormat
4: xlDMYFormat
9: xlSkip Column
I have tested in by using a text file but if you create a CSV file using
Excel, it won't work. I think the Excel will stored the data format.
For the 2nd approach, i think you have to make sure what you import is a
text, i mean the date it show is "04/06/2007" should align to left, not
right, otherwise, if it is on right align, that means Excel converted it to
date already. The use the approach 1 for force it as text. the argument is
Array(2,2). My bad experience is that it is a "MIX", those excel cannot
convert, will leave it a text but those can convert it WRONGLY converted.
like 1/2/07 and turn to 1 Feb07 but actualy is 2Jan07.
So if it is really a text field, then it's not difficult to use either left,
right or intri function and recombine it to whatever date format. Depending
on your original format. Or use text to column to split them then use date
function to recombine is quite easy.
Please let me know if you need help.
regards
Leung
"kiwis" wrote:
> Hi Tom,
>
> the changing format does not work in my case.
>
> I got the raw data in CSV file from some program that put date as dd/
> mm/yyyy.
> I did not key in the dates in the raw data.
> If i just do a1-b1, i will get #VALUE! error in the cell
>
> Even if i set the format to mm/dd/yyyy, 28/12/2002 still remains as
> 28/12/2002 instead of 12/28/2002.
> The others say 2/4/2002 will change to 4/2/2002
>
> so is there some way i can write a macro to set the format to mm/dd/
> yyyyy manually?
> maybe split up the date into individual components & then regoup them
> into mm/dd/yyyy
>
> thank you
>
> On Apr 26, 5:04 pm, "Tom Myers" <toma...@hotmail.com> wrote:
> > Kiwis,
> >
> > You are making this to difficult. No programming required.
> >
> > If you simply want the age between these dates in number of days then a
> > format change is not necessary.
> >
> > Simply format the cell, lets say cell(a3), that you want the age in days in
> > to number format 0 (integer with no decimal) and then in that cell the
> > formula =a1-a2 will produce the age in days.
> >
> > This will work the same in column b as well.
> >
> > The date format is not relevant - in fact you could even use the formula
> > =a1-b2 and it would produce the same result.
> >
> > You could also create a nested function to determine the age in Years,
> > Months and Days if desired for products of age beyond one month. You would
> > have some difficulty in being exact in the number of months due to the
> > variation in a month from 28 to 31 days. This could be done however.
> >
> > Tom (Big T Cougar) Myers
> >
> > "kiwis" <kiwiii...@gmail.com> wrote in message
> >
> > news:(E-Mail Removed)...
> >
> >
> >
> > > Hi
> >
> > > I need some help with the date format.
> >
> > > I have date in dd/mm/yyyy format & i want to find the age of the
> > > product.
> > > I think i need to change the date to mm/dd/yyyy before i can use my
> > > formula
> > > to find the age.
> >
> > > sample of my data what i need i think
> > > column b column c
> > > 25/12/2002 12/25/2002
> > > 5/2/2001 2/5/2001
> >
> > > how do i write a macro to convert the date to the format i want?
> >
> > > any ideas or solutions is welcomed
> >
> > > Thank you
> >
> > > rgds
> > >kiwis- Hide quoted text -
> >
> > - Show quoted text -
>
>
>
|
Similar Threads
