Date issues

davewatson86

davewatson86

New Member
Joined
Jul 8, 2019
Messages
30
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
Code: 
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.
Code: 
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

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

any help is greatly appreciated

Dave
 
Last edited:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
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?
 
Upvote 0
Its because VBA tends to be more inclined to think you are American so use MM/DD/YYYY. You could try this:

Code: 
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
 
Last edited:
Upvote 0
MARK858 said:
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?
Click to expand...

100% some are DD/MM/YYYY and some MM/DD/YYYY i would post up the data but its 202996 rows of data
 
Upvote 0
steve the fish said:
Its because VBA tends to be more inclined to think you are American so use MM/DD/YYYY. You could try this:

Code: 
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
Click to expand...

Thanks Steve the fish but this just made the whole column #VALUE ! including the table header.
 
Last edited:
Upvote 0
MARK858 said:
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?
Click to expand...

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

i copied this into cell R2 and it came up "Date" then when i dragged it down it came up with [TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl66, width: 83, align: right"][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl66, width: 83, align: right"]450965[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
in every cell i dragged it to.

thank you for your help as im stumped.
 
Upvote 0
Have you tried Text to Columns?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Excel shows wrong date format in cell
Replies
5
Views
103
dmt32
D
D
  • Solved
Matching TODAY() using VLOOKUP
Replies
7
Views
158
Peter_SSs
Peter_SSs
H
  • Solved
VBA and date formatting
Replies
8
Views
92
Joe4
Joe4
A
  • Question Question
Changing the date format in InputBox to AutoFilter by
Replies
1
Views
192
6StringJazzer
6StringJazzer
H
  • Question Question
Macro to Open File and limit selection where files contains "Parts" in C
Replies
4
Views
242
howard
H

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,620
Latest member
dsubash

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top