Image by Editor | Midjourney

When working with Excel files in Python, date formatting often requires special attention. While Excel provides the TEXT() function for date formatting, achieving the same results programmatically requires understanding how openpyxl handles date number formats. In this article, we’ll explore how to convert dates into different display formats using Python and openpyxl, focusing on common patterns like month/year combinations.

Setting Up the Basic Structure

To begin, we’ll create a workbook containing sample dates that we’ll format in different ways. We’ll set up a simple structure with columns for our original dates and three different format variations: MM/YYYY (e.g., 01/2024), MMM YYYY (e.g., Jan 2024), and MMMM YYYY (e.g., January 2024).

from datetime import datetime, date
from openpyxl import Workbook
from openpyxl.styles import Font

# Create a new workbook and select active sheet
wb = Workbook()
ws = wb.active
ws.title = "Date Formats"

# Add headers
headers = ['Original Date', 'MM/YYYY', 'MMM YYYY', 'MMMM YYYY']
for col, header in enumerate(headers, 1):
    ws.cell(row=1, column=col, value=header)
    ws.cell(row=1, column=col).font = Font(bold=True)

# Add sample dates
sample_dates = [
    date(2024, 1, 15),
    date(2024, 2, 28),
    date(2024, 3, 10),
    date(2024, 4, 5),
    date(2024, 5, 20)
]

# Add dates to first column
for row, dt in enumerate(sample_dates, 2):
    ws.cell(row=row, column=1, value=dt)

wb.save('date_formats_step1.xlsx')

In this initial setup, we:

• Create a new Excel workbook with a sheet titled “Date Formats”
• Add column headers with bold formatting
• Populate the first column with sample dates
• Use Python’s date class to ensure proper date handling

When you open the generated Excel file, you’ll see:

Image By Author

This gives us a clean foundation to build upon as we implement different date formatting options in the next section. 

Applying Different Date Formats

Next, we’ll implement various date formats using openpyxl’s number format system. These formats correspond to Excel’s built-in date display patterns, allowing us to show dates in several standard month/year combinations.

from openpyxl import load_workbook

# Load the workbook
wb = load_workbook('date_formats_step1.xlsx')
ws = wb.active

# Define number formats for different date displays
date_formats = {
    2: 'mm/yyyy',    # Column B: 02/2024
    3: 'mmm yyyy',   # Column C: Feb 2024
    4: 'mmmm yyyy'   # Column D: February 2024
}

# Copy dates and apply formats
for row in range(2, ws.max_row + 1):
    original_date = ws.cell(row=row, column=1).value
    
    # Apply each format to respective columns
    for col, format_code in date_formats.items():
        cell = ws.cell(row=row, column=col)
        cell.value = original_date
        cell.number_format = format_code

wb.save('date_formats_step2.xlsx')

The code above introduces three key date formatting patterns:

• mm/yyyy: Displays month as a two-digit number with year (01/2024)
• mmm yyyy: Shows abbreviated month name with year (Jan 2024)
• mmmm yyyy: Uses full month name with year (January 2024)

By setting the number_format property of each cell, we instruct Excel how to display the date values. The dates maintain their original values internally while presenting different formats to users.

Here’s how the Excel file looks after applying these formats:

Image By Author

The formatted dates provide clear and consistent presentation options that can adapt to various reporting needs. Each format serves specific purposes – the numeric format works well for sorting, abbreviated months save space, and full month names offer maximum readability. 

Optimizing Column Layout and Alignment

In the final step, we’ll enhance the readability of our date formats by adjusting column widths and aligning the content. These adjustments ensure that each date format displays properly without text overflow or awkward spacing.

from openpyxl import load_workbook
from openpyxl.styles import Alignment

# Load the workbook
wb = load_workbook('date_formats_step2.xlsx')
ws = wb.active

# Adjust column widths and alignment
for col in ws.columns:
    max_length = 0
    column = col[0].column_letter
    
    # Find max length in column
    for cell in col:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(str(cell.value))
        except:
            pass
    
    # Set width and alignment
    adjusted_width = (max_length + 2)
    ws.column_dimensions[column].width = adjusted_width
    
    # Center align all cells in column
    for cell in col:
        cell.alignment = Alignment(horizontal='center')

wb.save('date_formats_final.xlsx')

This final formatting step accomplishes several goals:

• Calculates optimal column width based on content length
• Centers all date values for consistent presentation
• Adds slight padding to prevent text crowding
• Ensures full month names display without truncation

The result shows our dates in a clean, professional format:

Image By Author

This completes our date formatting implementation. Each date now displays in three distinct formats while maintaining a polished, consistent layout. 

Conclusion

Python and openpyxl provide powerful tools for formatting dates in Excel, offering an efficient way to standardize date presentations across large datasets. The techniques shown here demonstrate how to:

• Structure date data with different month/year formats
• Apply Excel-compatible number formatting
• Fine-tune the visual presentation of date columns

Here’s a quick reference for the date formats we covered:

date_formats = {
    'Numeric': 'mm/yyyy',      # 01/2024
    'Short Month': 'mmm yyyy', # Jan 2024
    'Full Month': 'mmmm yyyy'  # January 2024
}

By combining these formatting patterns with openpyxl’s styling capabilities, you can create Excel files that present dates consistently and professionally. The automated approach saves time when working with multiple workbooks or large datasets that require standardized date formatting.

For additional Excel automation techniques using Python and openpyxl, check out our related guides on:

1. How to Effectively Work with Excel Files in Python: Pandas vs Openpyxl Guide
2. How to Create Color-Coded Progress Bars in Excel with Openpyxl
3. How to Implement Excel-Style Conditional Logic with Openpyxl