How to Format the Date Formula in Excel?

Formatting a date formula in Excel involves two key steps: first, using a formula to create a date value, and then applying a specific number format to display that date value in your desired format. Excel stores dates as serial numbers, and applying a format makes these numbers readable as dates.

1. Creating a Date Value with Excel Formulas

The primary way to create a date from individual year, month, and day components in Excel is by using the DATE function.

Understanding the DATE Function

The DATE function constructs a valid date from separate year, month, and day inputs.

• Syntax: =DATE(year, month, day)
• Arguments:
  • year: A number representing the year. Excel correctly handles two-digit years (e.g., 00-29 are 2000-2029, 30-99 are 1930-1999).
  • month: A number representing the month (1 for January, 12 for December). If you enter a number greater than 12, it will add that many months to the last month of the specified year.
  • day: A number representing the day of the month. If you enter a number greater than the number of days in the specified month, it will add that many days to the last day of the specified month.

Example from Microsoft Support:
As per Microsoft Support, the formula =DATE(C2,A2,B2) combines the year from cell C2, the month from cell A2, and the day from cell B2 and places them into one cell as a date. The result is initially a serial number that Excel recognizes as a date.

2. Formatting the Display of the Date Value

After you've created a date value using a formula like DATE(), you will need to change the number format (Format Cells) in order to display a proper date. This is crucial for presenting the serial number in a human-readable date format (e.g., "MM/DD/YYYY", "DD-MMM-YY").

Steps to Format Cells:

1. Select the Cell(s): Click on the cell or range of cells containing the date formula's output that you wish to format.
2. Open Format Cells Dialog Box:
   • Right-click on the selected cell(s) and choose "Format Cells..." from the context menu.
   • Alternatively, go to the "Home" tab on the Excel ribbon, and in the "Number" group, click the small arrow in the bottom-right corner or press Ctrl + 1.
3. Choose "Date" Category: In the "Format Cells" dialog box, go to the "Number" tab and select "Date" from the "Category" list on the left.
4. Select a Type: On the right side, you will see various predefined date formats (e.g., "3/14/2012", "March 14, 2012", "14-Mar-12"). Choose the one that suits your needs.
5. Custom Formatting (Optional): If the predefined formats don't meet your requirements, you can select "Custom" from the "Category" list. Here, you can define your own format using various date codes:
   • d: Day as a number without leading zero (1-31)
   • dd: Day as a number with a leading zero (01-31)
   • ddd: Day as an abbreviation (Sun-Sat)
   • dddd: Day as full name (Sunday-Saturday)
   • m: Month as a number without leading zero (1-12)
   • mm: Month as a number with a leading zero (01-12)
   • mmm: Month as an abbreviation (Jan-Dec)
   • mmmm: Month as full name (January-December)
   • yy: Year as two digits (00-99)
   • yyyy: Year as four digits (1900-9999)
   • You can combine these codes with separators like /, -, or spaces (e.g., dd-mmm-yyyy, m/d/yy).
6. Click OK: Once you've selected or defined your desired format, click "OK" to apply it to the selected cells.

Practical Example

Let's illustrate how the DATE function works and how its output is then formatted.

In the table above, the "Raw Result (Serial Number)" is what Excel stores internally. The "Formatted Result" columns show how you would display that serial number after applying different date formats via the "Format Cells" option.

By understanding both the DATE function for creating the date value and the "Format Cells" option for displaying it, you gain full control over how dates are presented in your Excel spreadsheets.

For more details on the DATE function, you can refer to the Microsoft Support page.