I want to change the data such as 9/4/2012(some cells with, green arrow in the right corner. ) into dd/mm/yyyy.

I want to standardize all the data of such column into dd/mm/yyyy , I format the columns as dd/mm/yyyy .

All data seem to appear displaying as dd/mm/yyyy. But issue cells , still having green arrow.

So I try to use DATE() function to format into date dd/mm/yyyy using DATE(MID(C13,5,4),MID(C13,3,1),MID(C13,1,1) for 9/4/2012 , but when converted appeared as 04/01/1906 (which is wrong).

PS: when I save as CSV, msg pop up like [test.csv may contain features that are not compatible with CSV. Do you want to keep the workbook in this format? ]

If I can format all the cells into correct format, I can proceed with my formula TODAY()-D11 , Now I am hitting error because of incorrect format.

2

3 Answers

Select the column, go Data > Text to Columns > next > next > select the Date option and then select the DMY or MDY according to how the data is stored.

That will convert all dates stored as text to real dates which you can then format and use in calculations.

3

=IF(ISTEXT(B2),DATE(RIGHT(B2,4),LEFT(B2,SEARCH("/",B2)-1),MID(B2,SEARCH("/",B2)+1,2)),DATEVALUE(TEXT(B2,"mm/dd/yyyy")))

And make sure to format the cells correctly (with dd/mm/yyyy).

Highlight the dates you want to change. Hit "Ctrl" and the "1" key. A pop-up box will open. Select the "Number" tab and select "Custom" from the bottom of the list. You will see two horizontal boxes: "Sample" and "Type" The type box is in mm/dd/yyyy format. Retype the m's, d's, and y's in the format you wish. If you don't want slashes, replace those with something more to your personal tastes. Go nuts!

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy