Date issues

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:

MARK858

Well-known Member
Joined
Nov 12, 2010
Messages
11,312
Office Version
365, 2010
Platform
Windows, Mobile
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

Well-known Member
Joined
Oct 20, 2009
Messages
7,716
Office Version
365
Platform
Windows
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:

davewatson86

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

New Member
Joined
Jul 8, 2019
Messages
30
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
Thanks Steve the fish but this just made the whole column #VALUE ! including the table header.
 
Last edited:

davewatson86

New Member
Joined
Jul 8, 2019
Messages
30
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
2126784425K0 807 228 AGUIDEKDJBumper sundry partsNR11102437Broadland Autocare01/12/2018VWDon't use2.20187.950100
2126784425K0 807 572 JGUIDEKDJBumper sundry partsNR11102437Broadland Autocare01/12/2018VWDon't use2.201826.340100
2126784425K0 807 724 BSUPP.PARTKDJBumper sundry partsNR11102437Broadland Autocare01/12/201810VW2.20185.930100
2126784425K0 853 601 F ULMVW SIGNKIBLogos/emblems/stripsNR11102437Broadland Autocare01/12/2018VWDon't use2.201832.250100
2126784425K2 941 006 KHEADLAMPECAHeadlights/lampsNR11102437Broadland Autocare01/12/201810VW2.2018123.80100
212678443WHT 001 812WHEEL BOLTFAHWheel boltsNR14102701Surlingham Garage01/12/201850VW/Audi/SEAT/Sko/NF2.201814.830400
212678444036 121 008 MXWATER PUMPRAEWater pumpsNR4103644Autodevotion Ltd01/12/201850VW/Audi/SEAT/Sko/NF2.201826.619.78100
212678444036 198 119 CREPAIR KITMFSToothed beltNR4103644Autodevotion Ltd01/12/201850VW/Audi/SEAT/Sko/NF2.201880.630100
212678445ZGB 00Q 000 SW4LUX TOILET ROLLS 40ZOCZConsumables otherNR6106417** DO NOT USE ** DO NOT USE ***01/12/201850VW/Audi/SEAT/Sko/NF2.20189.990100
2126784461J0 407 366 JJOINTFDPGuide jointPE33155772P.C & A.T Hudson01/12/2018MFDon't use2.201822.790100
21268161703F 903 023 EXALTERNATOREEAAlternator new/reconNR7106419Nigel Farrow Cars Ltd13/01/201850VW/Audi/SEAT/Sko/NF2.2018191.682.66100
2126816184F2 061 501 041FLOOR MATSZDCCRubber matsNR7133437swift fit Centre Salhouse Road Ltd13/01/201894Audi Accessories2.201829.170100
2126816193C0 127 400 DFUELFILTERRTAEngine repair individual/single partsNR5150924Truck East Ltd13/01/201850VW/Audi/SEAT/Sko/NF2.2018173.790100
2126816207E0 407 454 TXDRIVESHAFTFDEDrive shaftsNR3224057VAS Auto Specialist Centre Ltd13/01/201814NF2.2018236.8170.53100
212681743059 130 519WASHERNNDSealsNR2244069Retail Counter Sales13/01/201850VW/Audi/SEAT/Sko/NF2.201815.930300
2126818391H0 721 357MOUNTINGKEGMaster/slave cylinderNR6181692ANGLIA AUTOGAS LTD13/01/201850VW/Audi/SEAT/Sko/NF2.20181.030100
2126818401J2 721 332 ASTOPKTABody, individual/single partsNR6181692ANGLIA AUTOGAS LTD13/01/2018MFDon't use2.20183.350100
2126820073C0 611 763 ABRAKE PIPEBFRBrake pipes/hosesNR7184270Gotts Van & Car Service Centre13/01/2018MFDon't use2.201814.820100
212682154ZGB QAF PUR 20 5L205L PURPLE ANTIFREEZEZOCZConsumables otherNR14102703Yelverton Garage Ltd13/01/201850VW/Audi/SEAT/Sko/NF2.2018385.450100
2126821551K0 711 265 AQCABLERKZCablesNR17192122The Little Car Clinic (T.L.C)13/01/2018MFDon't use2.201829.590100

<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

Well-known Member
Joined
Oct 20, 2009
Messages
7,716
Office Version
365
Platform
Windows
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

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,254
Office Version
365
Platform
Windows
Have you tried Text to Columns?
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,716
Office Version
365
Platform
Windows
That would suggest your cells are already dates not text.
 

Forum statistics

Threads
1,077,855
Messages
5,336,782
Members
399,102
Latest member
chudson1

Some videos you may like

This Week's Hot Topics

Top