Date issues

[davewatson86]

Hello all

i have a report that is giving me dates in the format of DD.MM.YYYY i am trying to convert this to a date format that excel will work with but i am getting some strange tings happening.

i have used find replace to replace . with / but some dates are showing in a format of MM/DD/YYYY and some are showing as DD/MM/YYYY

this is the code i have used

WS_Data.Range("I:I").Replace What:=".", Replacement:="/", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

i have tried to use this code to change the format.

WS_Data.Range("I:I").NumberFormat = "dd/mm/yyyy:@"

however it faults stating "Unable to set the number format property of the Range class" i am thinking that its due to it being in a table but im not sure.

i did try this code to only effect the used cells rather than the whole column but had the same result

WS_Data.Range("I22", WS_Data.Range("I2").End(xlDown)).NumberFormat = "dd/mm/yyyy:@"

any help is greatly appreciated

Dave

 

[MARK858]

I doubt if some are mm/dd/yyyy and others dd/mm/yyyy, I would think that they are all US dates (I am assuming you are in the UK or Australia at the moment). Can you check the source first before we make any changes?

 

[steve the fish]

Its because VBA tends to be more inclined to think you are American so use MM/DD/YYYY. You could try this:

With WS_Data    
    lr = .Range("I" & .Rows.Count).End(xlUp).Row
    Set x = .Range("I1:I" & lr)
    x = Evaluate(Replace("IF(@="""","""",IFERROR(DATE(20&RIGHT(@,2),MID(@,4,2),LEFT(@,2)),@))", "@", x.Address(External:=True)))
    .Range("I1:I" & lr) = x
End With

 

[davewatson86]

I doubt if some are mm/dd/yyyy and others dd/mm/yyyy, I would think that they are all US dates (I am assuming you are in the UK or Australia at the moment). Can you check the source first before we make any changes?

100% some are DD/MM/YYYY and some MM/DD/YYYY i would post up the data but its 202996 rows of data

 

[davewatson86]

Its because VBA tends to be more inclined to think you are American so use MM/DD/YYYY. You could try this:

With WS_Data    
    lr = .Range("I" & .Rows.Count).End(xlUp).Row
    Set x = .Range("I1:I" & lr)
    x = Evaluate(Replace("IF(@="""","""",IFERROR(DATE(20&RIGHT(@,2),MID(@,4,2),LEFT(@,2)),@))", "@", x.Address(External:=True)))
    .Range("I1:I" & lr) = x
End With

Thanks Steve the fish but this just made the whole column #VALUE ! including the table header.

 

[davewatson86]

I doubt if some are mm/dd/yyyy and others dd/mm/yyyy, I would think that they are all US dates (I am assuming you are in the UK or Australia at the moment). Can you check the source first before we make any changes?

here is a small section of the data after i have run the find replace code

212678442	5K0 807 228 A	GUIDE	KDJ	Bumper sundry parts	NR11	102437	Broadland Autocare	01/12/2018	VW	Don't use	2.2018	7.95	0	1	0	0
212678442	5K0 807 572 J	GUIDE	KDJ	Bumper sundry parts	NR11	102437	Broadland Autocare	01/12/2018	VW	Don't use	2.2018	26.34	0	1	0	0
212678442	5K0 807 724 B	SUPP.PART	KDJ	Bumper sundry parts	NR11	102437	Broadland Autocare	01/12/2018	10	VW	2.2018	5.93	0	1	0	0
212678442	5K0 853 601 F ULM	VW SIGN	KIB	Logos/emblems/strips	NR11	102437	Broadland Autocare	01/12/2018	VW	Don't use	2.2018	32.25	0	1	0	0
212678442	5K2 941 006 K	HEADLAMP	ECA	Headlights/lamps	NR11	102437	Broadland Autocare	01/12/2018	10	VW	2.2018	123.8	0	1	0	0
212678443	WHT 001 812	WHEEL BOLT	FAH	Wheel bolts	NR14	102701	Surlingham Garage	01/12/2018	50	VW/Audi/SEAT/Sko/NF	2.2018	14.83	0	4	0	0
212678444	036 121 008 MX	WATER PUMP	RAE	Water pumps	NR4	103644	Autodevotion Ltd	01/12/2018	50	VW/Audi/SEAT/Sko/NF	2.2018	26.6	19.78	1	0	0
212678444	036 198 119 C	REPAIR KIT	MFS	Toothed belt	NR4	103644	Autodevotion Ltd	01/12/2018	50	VW/Audi/SEAT/Sko/NF	2.2018	80.63	0	1	0	0
212678445	ZGB 00Q 000 SW4	LUX TOILET ROLLS 40	ZOCZ	Consumables other	NR6	106417	** DO NOT USE ** DO NOT USE ***	01/12/2018	50	VW/Audi/SEAT/Sko/NF	2.2018	9.99	0	1	0	0
212678446	1J0 407 366 J	JOINT	FDP	Guide joint	PE33	155772	P.C & A.T Hudson	01/12/2018	MF	Don't use	2.2018	22.79	0	1	0	0
212681617	03F 903 023 EX	ALTERNATOR	EEA	Alternator new/recon	NR7	106419	Nigel Farrow Cars Ltd	13/01/2018	50	VW/Audi/SEAT/Sko/NF	2.2018	191.68	2.66	1	0	0
212681618	4F2 061 501 041	FLOOR MATS	ZDCC	Rubber mats	NR7	133437	swift fit Centre Salhouse Road Ltd	13/01/2018	94	Audi Accessories	2.2018	29.17	0	1	0	0
212681619	3C0 127 400 D	FUELFILTER	RTA	Engine repair individual/single parts	NR5	150924	Truck East Ltd	13/01/2018	50	VW/Audi/SEAT/Sko/NF	2.2018	173.79	0	1	0	0
212681620	7E0 407 454 TX	DRIVESHAFT	FDE	Drive shafts	NR3	224057	VAS Auto Specialist Centre Ltd	13/01/2018	14	NF	2.2018	236.81	70.53	1	0	0
212681743	059 130 519	WASHER	NND	Seals	NR2	244069	Retail Counter Sales	13/01/2018	50	VW/Audi/SEAT/Sko/NF	2.2018	15.93	0	3	0	0
212681839	1H0 721 357	MOUNTING	KEG	Master/slave cylinder	NR6	181692	ANGLIA AUTOGAS LTD	13/01/2018	50	VW/Audi/SEAT/Sko/NF	2.2018	1.03	0	1	0	0
212681840	1J2 721 332 A	STOP	KTA	Body, individual/single parts	NR6	181692	ANGLIA AUTOGAS LTD	13/01/2018	MF	Don't use	2.2018	3.35	0	1	0	0
212682007	3C0 611 763 A	BRAKE PIPE	BFR	Brake pipes/hoses	NR7	184270	Gotts Van & Car Service Centre	13/01/2018	MF	Don't use	2.2018	14.82	0	1	0	0
212682154	ZGB QAF PUR 20 5L	205L PURPLE ANTIFREEZE	ZOCZ	Consumables other	NR14	102703	Yelverton Garage Ltd	13/01/2018	50	VW/Audi/SEAT/Sko/NF	2.2018	385.45	0	1	0	0
212682155	1K0 711 265 AQ	CABLE	RKZ	Cables	NR17	192122	The Little Car Clinic (T.L.C)	13/01/2018	MF	Don't use	2.2018	29.59	0	1	0	0

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col span="2"><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[steve the fish]

It doesnt do that when i run it. Works perfectly fine. Just check this formula works:

=IF(ROW(I1:I5),IF(I1="","",IFERROR(DATE(RIGHT(I1,4),MID(I1,4,2),LEFT(I1,2)),I1)))

Drag down as far as required.

 

[davewatson86]

It doesnt do that when i run it. Works perfectly fine. Just check this formula works:

=IF(ROW(I1:I5),IF(I1="","",IFERROR(DATE(RIGHT(I1,4),MID(I1,4,2),LEFT(I1,2)),I1)))

Drag down as far as required.

i copied this into cell R2 and it came up "Date" then when i dragged it down it came up with

450965 <tbody> </tbody>

<tbody>
</tbody>

in every cell i dragged it to.

thank you for your help as im stumped.

 

[Fluff]

Have you tried Text to Columns?

 

[steve the fish]

That would suggest your cells are already dates not text.