Sometimes when you download bank statements into excel you get weird dates, particularily January to September for no apparent reason. This vba helps with converting them. Not recommended for dates in the first decade of this century.

`=TEXT(DATE(IF(YEAR(A2)-YEAR(NOW())>=-1,YEAR(A2),2000+DAY(A2)),TEXT(IF(YEAR(A2)-YEAR(NOW())>=-1,MONTH(A2),RIGHT(YEAR(A2),2)),"00"),IF(YEAR(A2)-YEAR(NOW())>=-1,DAY(A2),MONTH(A2))),"dd-mmm-yyyy")`

ok so there’s a lot in this one….

=Text([Cell in Excel],”dd-mmm-yyyy”) takes a cell and forces the date format to be like 03-MAR-2017

Year – pulls out the year in a date

day – pulls out a day in a date

month – pulls out a month in the date.

=if(year([Cell in Excel])-year(now())>=-1,..,..) this one checks if the year of the date you’ve provided is more than a year different from the year of the current date. This helps in catching those dates formatted in the mac standard (the stuff showing as 2008).

Date(Y,M,D) takes numeric values for year, month and day to create a date.

The rest of this repeats the formulas above to essentially reformat any dates appearing more than a year out.

A2 is the transaction date in your