Question

write the steps of change the date format to DD/MM/YYYY

friends give me answer fat my exam is going on ​

Answer 1

Answer:

Step 1: Extract the year

=LEFT(A1,4) => 2018

Step 2: Extract the day

=RIGHT(A1,2) => 25

RIGHT function to extract the day

Step 3: Extract the month

This step is a little bit more difficult because you must extract 2 characters in the middle of your string. In that case, you use the function MID.

=MID(A1,5,2) => 12

You extract 2 characters from the 5th position

MID function extract the month

Step 4: Convert each part as a date

Now we are going to gather each part of the date to return a "real" date. To do this we use the DATE function.

=DATE(B1,D1,C1)

DATE function creates a real date with the 3 arguments day, month and year

Convert without formula

But you can convert YYYYMMDD date, or any other date format, to your local date format with the tool Text to Columns.

1. Select your date(s) to convert

Selection of the column with the dates

2. Go to the menu Date > Text to Columns

Menu Text to Columns

3. Skip the 2 first steps because you don't want to split anything in your column.

Skip the 2 first steps

4. In the 3rd step is the most important. With the dropdown list, you select the type of date format that you have in your column. So, in our example, we are going to select YMD because our dates are written YYYYMMDD.

Option to select the date format to return

5. Press Finish and that's it

Date convert in standard format without formula