Put 08/10/2013 23:58 in to a cell that has been formatted as text. Now remove the 20 manually (08/10/13 23:58). Excel does nothing to the cell. Now use find and replace and look for /2013 and replace with /13... Excel will reformat the cell from text to a date.... argh!
FYI the date coming out in my report is in a US format and I'm UK... this is the 10th August 2013 at 23:58....
If I could just switch off this function I would be able to clean my data up using Replace, some clever formulas and reformat it to the UK standard then switch on the cell formatting to Date.
The date format in the column that I have to deal with is as follows....
mm/dd/yyyy hh:mm AM
mm/dd/yyyy hh:mm PM
dd/mm/yyyy hh:mm AM
dd/mm/yyyy hh:mm PM
I need all to be one of the following dd/mm/yyyy or dd/mm/yyyy hh:mm or dd/mm/yyyy hh:mm:ss
Any tips or help would be appreciated