date format help needed

[kiwis]

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

 

[Guest]

It should be no problem for you to use general formula in Excel for whatever
date format. FYI, excel store date as numeric and starting from 1/1/1900.
then today it actually store it as 39198, which is the 39198th day.

Anyway in VBA if you want to change format for date value,

format(date,"dd/mm/yyyy")

the string "dd/mm/yyyy" can be replaced by the date format in the cell
format custom format dialog box.

hope this help.

Leung

 

[Tom Myers]

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]

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

 

[Guest]

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