Fixing my date format

[robertdino]

I have a huge excel sheet with dates in MM/DD/YYYY format (ex. 29/05/2020)

Problem is that some of the cells are in M/DD/YYYY format which is possible when the month number is a unit (less than 10), obviously (ex. 6/05/2020)

Now I want to change all cells to DD/MM/YYYY format and I am unable to do this because all cells that have a number in MM beyond 12 convert properly but those with MM as 12 or below remain in the same MM/DD/YYYY format.

Can someone help? I bought Kutools too.

 

[Anthony47]

Do you import those data from a csv (or txt) file?

 

[robertdino]

From multiple files in the past and now I need to export this data with these dates in DD/MM/YYYY format.

 

[Anthony47]

In Excel, a date is a date whichever format you use to display it, so probably you "only" need to export that column as a date and not as a string of text.
The biggest problem we have is that now when we read 06/01/yyyy we don't know whether the original date was Jan-6 or Jun-1 (because both were translated in 06/01/2014)
Is there a way to understand that? Are they still in the original sequence or have they been mixed?
I mean: from the image it is clear that your dates where May-30, May-31, Jun-1, Jun-2 and so on; but I see twice a "06/01/2014": do we have to translate both as June-01-2014, or one of those should say "Jan-6-2014"?
If you format as Number" one of the 06/01/2014, what do you read: 41791 or 41645? or what?

 

[SanjayGMusafir]

I have a huge excel sheet with dates in MM/DD/YYYY format (ex. 29/05/2020)

Problem is that some of the cells are in M/DD/YYYY format which is possible when the month number is a unit (less than 10), obviously (ex. 6/05/2020)

Now I want to change all cells to DD/MM/YYYY format and I am unable to do this because all cells that have a number in MM beyond 12 convert properly but those with MM as 12 or below remain in the same MM/DD/YYYY format.

Can someone help? I bought Kutools too.

This shall require multiple steps - It may sound long but it's actually very easy.

1. First keep the data back to MM/DD/YYYY or DD/MM/YYYY format )which is your system's default format right now) just to keep uniformity.
2. Then convert whole column to number - I generally select and click , (comma) to do that.
3. Then sort column ascending or descending - sorting number and Text separate (it shall ask you while sorting.
4. Now you have got all the problematic entries at one place together.
5. Simply convert them to dates using default format of step 1 - This step might require some alternative solutions if not getting the desired result.
   1. Add a temporary helper column and put a formula

      =TEXT(A2,"mm/dd/yyyy")

      assuming dates start from cell A2 in column A:A
   2. Note: "mm/dd/yyyy" or "dd/mm/yyyy" in the above formula must match your system's default date format
   3. Now you can simple copy the values from helper column and paste as values in Corresponding Column A cells
6. Once done that - Now you convert your whole column A to any format you want to.

Try it. Hope it helps

 

[Dave Patton]

N.B. My systems Regional Settings for dates are short date dd-mm-yy (28-08-22) and long date dd-mmm-yyyy (28-Aug-2022)

1. can you start with the original text before your system converted some of the data?
2. What are your regional settings for Dates?
3. I used Data Text to Columns on B2:B6 to yield C2:C6
4. You can export just the values and the recipient can format to their preference

T202208a.xlsm
	A	B	C	D	E
1		Text	Dates		Value
2	TEXT in B	29-5-2022	29-May-22	29-May-22	44710
3	TEXT in B	05-05-2022	05-May-22	05-May-22	44686
4	TEXT in B	15-1-2022	15-Jan-22	15-Jan-22	44576
5	TEXT in B	15-12-2022	15-Dec-22	15-Dec-22	44910
6	TEXT in B	15-5-2022	15-May-22	15-May-22	44696
7
8	Mixed Text and Values	29-5-2014	29-May-2014		41788
9	Mixed Text and Values	30-5-2014	30-May-2014		41789
10	Mixed Text and Values	06-01-2014	01-Jun-2014		41791
11	Mixed Text and Values	06-02-2014	02-Jun-2014		41792
12
8a

Cell Formulas
Range	Formula
D2:D6	D2	=DATEVALUE(B2)
E2:E6,E8:E11	E2	=C2
C8:C11	C8	=IF(ISNUMBER(B8),DATE(YEAR(B8),DAY(B8),MONTH(B8)),DATEVALUE(B8))

 

[robertdino]

I think I know how to fix this.

Can someone just tell me how I can add "0" prefix for cells with singular MONTH digits so I can change all M/DD/YYYY to MM/DD/YYYY format?

In other words, I want to convert all entries like this

6/14/2014

TO

06/14/2014

All these entries are in the same column. I just need a find/replace regex, someone help.

 

[Anthony47]

If you are sure that this is the trick then
-in a free colomn, row 2, insert the formula

=IF(FIND("/",A2)=2,"0"&A2,A2&"")

This assume your text is in column A, from A2 down; adapt the formula for different column

Copy the formula down

Copy the result of the formulas and paste-special /Values to the initial column; then you may delete the formulas

For sure this method will fail if the column contais text (such as 05/31/2014) and dates (those cells that will look as numbers if you format as Numbers); one of the reason for failure is that at the moment we don't know whether a date now shown as 06/01/yyyy was initially a 06/jan/yyyy or a 01/jun/yyyy

 

[Alex Blakenburg]

I think I know how to fix this.

Are you sure ?

By the image you posted my guess is that your source file is in the format m/dd/yyyy and your region setting is dd/mm/yyyy.

If nobody has made manual adjustments to the data loaded a Text to Columns conversion should fix both the Text and non-Text items.
Just select date format = MDY when you do the conversion.

Your data seems to be in sequential date order so it should be relatively easy to do a sensibility check on the conversion,

Output Example

Book1
	A	B
1	Before	After Text to Columns
2	5/29/2014	29/05/2014
3	5/30/2014	30/05/2014
4	5/31/2014	31/05/2014
5	06/01/2014	1/06/2014
6	06/02/2014	2/06/2014
7	06/03/2014	3/06/2014
Date Conversion

 

[robertdino]

Are you sure ?

By the image you posted my guess is that your source file is in the format m/dd/yyyy and your region setting is dd/mm/yyyy.

If nobody has made manual adjustments to the data loaded a Text to Columns conversion should fix both the Text and non-Text items.
Just select date format = MDY when you do the conversion.

Your data seems to be in sequential date order so it should be relatively easy to do a sensibility check on the conversion,


View attachment 72798

Output Example

Book1
	A	B
1	Before	After Text to Columns
2	5/29/2014	29/05/2014
3	5/30/2014	30/05/2014
4	5/31/2014	31/05/2014
5	06/01/2014	1/06/2014
6	06/02/2014	2/06/2014
7	06/03/2014	3/06/2014
Date Conversion

>By the image you posted my guess is that your source file is in the format m/dd/yyyy and your region setting is dd/mm/yyyy.
Yes, right.

You are the man, this fixed everything.