2 digit year dates to 4 digit

[John_McClane]

Hello,

Thanks in advance for looking at my query.

I have a report that lists expiry dates for peoples training courses. It is generated by someone else, I cannot change the way the report is made (even though that would be the simple solution)

I am creating a macro to tidy up the report and to conditionally format the dates to have a traffic light system, however because the dates are 2 digit year dates (e.g 04/04/14) the conditional formatting does not work. Of course, I can change the dates by using error checking but that doesn't seem to record so when I run the macro the dates do not change. They are all xx/xx/20xx. Is there any code I can add to change all the dates to 4 digit with '20' at the beginning of the year?

Any ideas to get the conditional formatting to work would be great! Thank you very much,

 

[mohan.pandey87]

Try to format cell with

mm/dd/yyyy
OR
dd/mm/yyyy

 

[John_McClane]

Hi Mohan,

No luck with this I'm afraid, I think because the date has been entered as text Excel doesnt know if the start of the year is 20 or 19 etc.

Any other ideas?

 

[chetanmehra1989]

Sub chetan()
[B2].Formula = _
"=iferror(DATE(IFERROR(YEAR(rc[-1]),RIGHT(rc[-1],4)),IFERROR(DAY(rc[-1]),SUBSTITUTE(LEFT(rc[-1],2),""/"","""")),IFERROR(MONTH(rc[-1]),LEFT(RIGHT(rc[-1],7),2))),"""")"
Columns("B:B").NumberFormat = "dd/mm/yyyy"
end sub

 

[mikerickson]

A helper column with =DATEVALUE(A1) will convert all those strings into Excel serial dates.