In this lesson, we will learn how to change the date format in Excel, and then we will learn about the function that collects the day, month, and year in one cell starting from several cells.
To clarify the idea, we will prepare the following data in Excel.
Change the date format in Excel
To change the date format in Excel, first, we go to the cell whose format we want to modify and click on this cell with the right mouse button.
A list of options will drop down from which we choose “Format Cells”.
The following interface will appear:
On the right, we notice the interface called “Type” from which we can choose the date display style.
On the left, we notice a list containing the option “Custom”. This option allows us to format the date in the way we see fit.
Determining the date format
We notice that there is a general format for the date “d/m/y” such that:
- The letter “d” indicates the number of digits in which the day of the date will appear. If “d” is a single digit, for example, the number 2, then the day of the date will appear as “01”, which corresponds to the pattern “dd”.
- The letter “m” indicates the number of digits in which the month of the date will appear.
- The letter “Y” indicates the number of digits in which the year of the date will appear. Since the year is generally composed of four digits, the year will appear in the following format “yyyy”.
- So, to change the date format, we can manually convert the digit for the day, date, or year such that:
- When changing today’s date from “d” to “dd”, it will appear as a two-digit number. If today’s date is “1”, when changing, it will appear as “01”.
- When changing today’s date from “d” to “ddd”, an abbreviation for the day’s name will appear. If today’s date is Sunday, the abbreviation “Sun” will appear.
- When changing today’s date from “d” to “dddd”, the day’s name that the date represents will appear as a full name. If today’s date is Sunday, then when changing it, “Sunday” will appear.
- In the same way, the same rules apply to the month and year.
In our example, if we put the date in the following format “dddd/mmmm/yyyy”.
Then we click “Ok” and see the result.
Function to add day, month, and year to one date in Excel
To clarify the idea, we have the following data:
To add day, month, and year to form a complete date in the “Date” field, we use the “DATE” function, which we place in the field where we want the date to appear.
Then we will select the year field.
As the function requires, we will place a comma and then select the month field.
After that, we will select the last remaining field, which is the day field, after placing a comma before it.
Then we will close the bracket. Then we press “Enter” and notice the result, the full date appears.
To apply the process to all the remaining elements, we select the resulting cell and then drag it down to the following cells.
We can certainly apply the process of changing the date format that we mentioned above to them through the following steps:
• We select the resulting cells completely to change the date format in them.
• We click on the cells with the right mouse button, and from the drop-down list we choose “Format Cells”.
• From the interface that will appear, we choose “Type” and then change the date format to appear in the way we want.
• Then we click “Ok”, and all dates will appear in the format we chose.
Dear reader, if you liked the article, do not forget to subscribe to our YouTube channel, which provides all new in the field of technical and completely free training courses.
You can also browse our website to access the blog and read technical topics, or learn about the training courses offered by the site.
To access the full course “Excel Course” on YouTube, click here.
