How to Format Date in dd/mm/yyyy Format in Excel

Excel is a powerful tool used for data manipulation and analysis, but working with dates can sometimes be tricky. Formatting dates in a specific format like dd/mm/yyyy is a common requirement for many users. This guide will walk you through the steps to format dates in this format accurately.

Step-by-Step Guide

When you need to change the date format in Excel, you can do so by right-clicking on the cell, row, or column that you want to modify. Select the Format Cells option, and then choose the appropriate format under the Date category, or create a custom format if the desired format is not available.

Using Custom Format

Right-click on the cell or column where you want to apply the date format. Select Format Cells from the context menu. Go to the Number tab on the Format Cells dialog box. Choose Custom from the Category list. Enter the format yyyy-mm-dd in the Type: field. Click OK. Enter the date as you normally would, for example, 04/13/2022. The date will display in the yyyy-mm-dd format.

Examples

Example 1: Changing Date Format for a Whole Column

Select the column containing the dates. Right-click on the selected column and choose Format Cells. Go to the Number tab and click on Custom from the Category list. Enter the format yyyy-mm-dd in the Type: field. Click OK to apply the format.

Example 2: Using Formulas for Specific Dates

Instead of changing the format for an entire column, you can use Excel formulas to convert specific dates. For example:

In a new column, use the following formula to convert a number representing a date in Excel (e.g., 44620) into the dd/mm/yyyy format. Use the formula DATE(INT(A1/10000), MOD(INT(A1), 10000)/100, MOD(A1, 100)) where A1 is the cell containing the date number.

Additional Tips

Excel provides other ways to format dates in various custom styles. To explore these options, right-click on the cell containing the date and select Format Cells. Then navigate to the Number tab and choose Date. From there, you can select from a range of pre-defined date formats or create a custom format in the Type: field.

By following these steps, you can easily change the date format to the dd/mm/yyyy format in Excel, making your data more presentable and easier to understand.