-1

How can one regularise the dates format for below dates?

I want all below dates in format "22-Feb-17"

NOTE: Please note that I've tried converting the format by going to change format in the main menu section, but it didn't work.

Also tried: right click> Format Cell> Date > selected "22-Feb-17". This also didn't work.

27/04/2017
27/04/2017
27/04/2017
27/04/2017
5-Feb-17
5-Feb-17
5-Mar-17
5-Mar-17
5-Apr-17
5-Apr-17
5-Aug-17
5-Aug-17
22/5/2017
22/05/2017
24/05/2017

Please advise.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Nitish
  • 23
  • 1
  • 6
  • 2
    I will bet you a doughnut that you have imported DMY into a MDY system and those 5-Feb-17 values should actually be 2-May-2017, the 5-Mar-17 should be 3-May-2017, the 5-Apr-17 should be 4-May-2017 and the 5-Aug-17 should be 8-May-2017. See [Excel VBA - Convert Text to Date](https://stackoverflow.com/questions/20375233/excel-vba-convert-text-to-date/30609676#30609676) for advice. –  Jul 01 '17 at 20:50
  • Not necessarily @Jeeped - my client has a database full of dates that were stored in varchar field, in all manner of formats. – Brian Milnes Dec 01 '17 at 12:08

2 Answers2

0
  • Go back to your original csv file, before it was ever opened and saved in Excel.
  • Then choose to Import the file (exactly where depends on your excel version). The Text import wizard will open and you will be able to specify the proper format of the dates in the csv file (eg. DMY).
  • Once they are properly imported as dates, you will be able to format the column however you wish.
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
0

Although it is definitely best to revert back to source and start again with an appropriate conversion, in case source is not available a formula conversion may serve:

=IF(ISTEXT(A1),DATE(2017,MID(A1,4,2),LEFT(A1,2)),DATE(2017,DAY(A1),MONTH(A1)))

This assumes (i) all dates are 2017 (easy enough to adjust if not) (ii) dates containing forward slashes are correct but in text format (iii) dates containing hyphens have the month and day switched around but are in date format.

pnuts
  • 58,317
  • 11
  • 87
  • 139